skip to Main Content

Data Ambiguity…The Silent Report Killer

Hello P3 Nation, I’m excited to be back and writing a technical post again…it’s been too LONG! Today I want to talk to you about a real-world business problem and solution I encountered last year. I think it’s a scenario at least a few of you will relate to. I was working on a project for a client, and needed to create a Power BI report for them.

However, the client didn’t think the active filters in the report were easy to identify. Now this is a scenario I’ve encountered a few times over the years, whether it’s been in Power BI, Excel, or other reporting tools. Overall it comes down to this, data ambiguity can render a report A) Untrustworthy, B) Dangerous, or C) Useless. Personally, I think a well-designed Power BI report has clear “enough” call-outs on the filters. Truth be told though, it only partially matters what we think. At the end of the day, we need to cater the report to the client’s needs.

Please click here to download the workbook to follow along.

Typical report layout I create. Primary sections for – Headers, Slicers, Visuals/Tables:


“I Can See (Filters) Clearly Now”

The client and I brainstormed, and we decided to create card visuals to identify filter selections. The beautiful thing about DAX, as mentioned in many articles on our site, is that it can easily return text values. I actually did something similar to this with Dynamic Titles when I posted about Power BI’s new Drill Through feature last year, that article can be found here. So, I essentially wanted to do something similar here, but to call out the filter selection for each slicer. The end result looked something like this

Same report with additional section for slicer filter selection:


The end result was a new section of the report, dedicated to calling out slicer selections. The BIGGEST reason the client wanted this, was for screenshots. They often took screenshots of this report, and pasted it into emails or slides to use in presentations. The result works well, and uses a bit of clever DAX to always return the right selections, no matter the combination of selections among the slicers.


To get the right output with DAX I utilized one of the newer functions, Selected Values. Selected Values is used whenever you use the What If feature in Power BI Desktop. In fact…if you look at my screenshots above, the Rolling Avg Months & Expense Ratio What Ifs are attached to Selected Values measures. Selected Values works like this, you give it a column reference, let’s say [Column A]. If there’s a selected value (E.g. single selection) on that column, it returns that value. Otherwise there’s an alternative result returned. Here’s what the DAX Measure looks like for the Expense Ratio Value.

     Expense Ratio Value = SELECTEDVALUE(‘Expense Ratio'[Expense Ratio], 0.50)

Selected Values is a fairly simple, and straight forward function. For a thorough and in-depth article on it’s uses and abilities, I’ll refer you to a post Marco Russo wrote over at SQLBI. I figured I could use this function to point to my slicer columns, and return a “No Selection” alternative result if there wasn’t. I thought that’s all I’d need, just rinse and repeat a bunch of Selected Values measures, and I’d be done! Not quite…I noticed a few issues after I did this.

Filter Issue Number One

I quickly realized that I was getting false positive filter selections when using certain slicers. As an example, whenever I made a specific selection on Employee Sales Region, sometimes the filter selection for Employee Team Name would change too! What was happening is there was a Sales Region with only a SINGLE Team name. It was triggering the Selected Value logic, because only a SINGLE VALUE was being given to it.

False positive filter selection, despite no selection on the slicer:


I needed my DAX measure to run ONLY when there’s an ACTIVE filter on the column. Selected Values doesn’t check to see if the column has an active filter on it, instead it’s only checking to see if there’s a single value from that column. Some additional business logic would need to be applied to it, to FIRST check for active filtering BEFORE running the Selected Values logic. I worked through a few variations, and eventually ended up using the DAX Function ISFILTERED.

     Employee Sales Region Selection = IF( ISFILTERED(‘Employee Details'[Employee Team Name]), SELECTEDVALUE(‘Employee Details'[Employee Team Name], “No Selection”), “No Selection”)

Filter Issue Number Two

ISFILTERED worked, and I no longer got a false positive filter selection! However, further testing unveiled a second problem. The other issue aired its ugly head whenever a slicer had multiple selections on it. Now my DAX is returning a false negative! I want it to return “Multiple Selections” if I have TWO or more slicer selections. This will be an easy enough fix actually.

The Employee Sales Region Selection DAX (above) has two conditions it’s checking. Condition one (ISFILTERED) is checking if there’s an ACTIVE filter on the column. If that condition is met, then it checks condition two (SELECTEDVALUES), which checks to see if there’s only ONE value coming from that column. If that second condition isn’t met, it returns “No Selection” at the inner most part of the query. All we have to do is update the alternative result in condition two, and change it to “Multiple Selections“.

     Employee Sales Region Selection = IF( ISFILTERED(‘Employee Details'[Sales Region]), SELECTEDVALUE(‘Employee Details'[Sales Region], “Multiple Selections”), “No Selection”)

BEFORE – Multiple selections on the slicer, but no indication of filters on the card:


AFTER – Multiple selections on the slicer, Multiple Selections indicator on card:


It’s also possible to return the entire list of selected values, instead of “Multiple Selections”. In fact, Rob has a great post on how to do JUST THAT. It utilizes a DAX Function called CONCATENATEX, and I’d recommend giving it a read. I didn’t go that route, as the client was happy just having multiple selections as the output for this scenario. There you have it folks, a great way to add a bit of clarity to your report. In case youever need a really obvious call-out for your slicer selections. I’m glad to be back in the technical saddle a bit on post writing, look forward to some more posts from me this spring!

Reid Havens

Reid Havens

Principal Consultant at PowerPivotPro.

This Post Has 38 Comments
  1. Excellent article – I use the ‘Selected Value to provide dynamic titles for reports. Is it possible to configure the Filter Selection Cards so that the ‘Filter Title’ precedes the ‘filtered value’ – believe that this is more user friendly and quicker way of reading the Filter Selection Cards.

    1. So, just curious why you would use “No Selection” as opposed to “All ….” as your alternate such that the value under Employee Sales Region would read “All Sales Regions”, Employee Team Name, “All Teams”. That is more specific to the information being displayed.

      1. Customer request was to have the display be no selection. It’s really a matter of preference. But yes, All would more closely match the All displayed in the slicer.

    2. You could certainly easily do that using a concatenate function in the DAX. The biggest reason was all that text wouldn’t have easily fit on a single line using the multi card visual.

  2. Hi Reid,

    I’m trying to understand the logic behind your post and am a little confused regarding the Filter Issue Number One. You show the following DAX measure:

    Employee Sales Region Selection = IF( ISFILTERED(‘Employee Details'[Sales Region]), SELECTEDVALUE(‘Employee Details'[Sales Region], “No Selection”), “No Selection”)

    The only column referenced here is ‘Employee Details'[Sales Region]’ shouldn’t that be something like ”Employee Details'[Sales Team]’ and the name be ‘ Employee Sales Team Selection ‘ instead or am I missing something here? I mean SELECTEDVALUE(‘Employee Details'[Sales Region] … can’t possibly reflect the selection of values on the sales team slicer.

    Best regards Jes

    1. Ah good catch! The logic remains the same, however you’re right. It should be the team name column. I updated to have it reflect that.

  3. Great post – thanks Reid.

    Just to confirm.. SELECTEDVALUE can only refer to Page level filters and not Report level filters? I believe the PBI development team is working on the option for Slicers to be applied (connected) to multiple reports as in Excel.

    1. Hi James, that’s “currently” correct. However we’re about to do a post VERY SOON about the universal slicer feature that is about to be released. Making this post even more relevant!

      1. Hi Reid, thanks. As luck would have it Microsoft announced the completion of the universal slicer feature yesterday. I look forward to your associated post.

  4. Hi Reid, Thank you very much. Great Article. Only one thing I can’t figure out is how you were able to get the selections to display horizontally in the multi-row card visual. I have both Card Title and Card in my format section of the visual where you have just Card. Any thoughts?

  5. Thanks for this interesting and helpful post! I like the Visual of the “Total Orders by Year, Quarter and Employee name”. What’s the name of this Visual in Power BI ? Where can I download this visual ? I searched in the Microsoft Power BI visual marketplace but didn’t find it 🙁

  6. Hi, I down loaded the workbook file to follow along as suggested in the article. Once it was unzipped there were no file associations attached to any of the files!!

    Was this meant to be this way or is there something I am missing??

    thanks ahead of time 🙂

    1. Hi Bryan, for some reason that file downloads as a .zip instead of a .pbix. If you change the file extension to this, it will open in Power BI Desktop

    1. Can we dynamically display the visualization based on this function? My requirement is let say we have sales and GP. Based on the selection I want to display either sales or GP.

  7. Excellent article,but I have a question.How does the line chart in your solution, including forecasting.Thanks

  8. Thanks for the great article, Reid. I am having a small issue when using this function for a slight variation in purpose. I used SelectedValue to show me the bar selected in a bar graph for monthly data but it does not work as soon as the drill down mode is turned on. My main purpose was to show which month is being drilled down into. Any suggestions for this? Thanks!

  9. Hi, this was very helpful in getting me started on filtering based on the slicer chosen. I need to take this a step further. When a user selects a sprint period, I need to be able to use the sprint’s start date (on of the fields in the table of the selectors value) to calculated each day’s date for the sprint period. I created a new table that has a row for each day in the sprint (sprints are a 2 week fixed period) so the table as 14 rows, on row for each day in the sprint. One of the rows is the the Selected Sprint’s Start date – derived using your formula Selected

    Start Date =
    IF (
    ISFILTERED ( ‘Sprints'[Sprint] ) && HASONEVALUE (‘Sprints'[Sprint] ),
    FIRSTNONBLANK(Sprints[Start Date],0))

    Id get a value in each row of the start date for the sprint chosen in the slicer. However, I cannot use this value to calculate anything, It appears to store as some kind of virtual variant and I cannot perform any calculations with it. What I need is to set the Column ‘Day Date’ as Selected Start Date + Day No. (Day Number is the row number in the Sprint Value table 1-14).

    This simply doesn’t work. Any ideas?

  10. Hi,
    First, this is amazing. I do have something that is throwing me for a loop. How would you compare the sales between team members or the sales between two regions?

    Something Like
    Steve’s Sales Nancy’s Sales Variance Variance %

    I am trying to do several KPI’s that involve comparisons against different segments of the business.

  11. GREAT article. I am pulling my hair out a little though on a variation of this problem and I am hoping you can help me to solve it. Basically I want to mimic the behavior of a standard slicer: regardless of what other filters there may be on the report (page-level, report-level, or other slicers on the page), if all available options are selected (or unselected…) then the slicer says “All”. Likewise I want my custom slicer-selection card to show “All” (not “Multiple Selections”).

    Example: I have a column called Store[Region] which has 9 total values: CALCULATE(DISTINCTCOUNT(Store[Region]), ALL(Store)) = 9
    I have a page-level filter on Store[Region] removing 3 of them. So ISFILTERED is always going to be TRUE thus my slicer-selection card based on your article always shows “Multiple Selections”. However if I add a slicer for [Region] to the page, it dutifully shows “All” until I actually choose one of the available options. How can I determine the same using DAX?

  12. The link to download the pbix file is broken. Would really appreciate it if a mirror is provided.

  13. I have region filter from one table and the region filter from another table, is it possible if I select something in one filter the same value should get selected in the other filter too?

  14. Hi, I have one slicer, It has 2 different values.For example A & B.if i select “A” i should get all data in the particular visualization, if i select “B” then i don’t want to get the values which are selected for “A”.How to create logic for this.

  15. I am working on a cube connection in Power BI and in my situation, I am working on two tables in the cube – One for Countries & Region and second one is for Segments. When I create filter for Region like APJ, it filters APJ countries in my next filter (which is country filter). Now I have a 3rd filter for Segment and I want it to filter based on the selected country but it shows all the unique segments for each country. I assume that the reason is different tables. I would appreciate any help on this issue. Please note that I have limited options as it is a cube connection like can’t create calculated columns or groups etc.

  16. Hi- I am creating a dynamic title using a measure in Power BI. The Title is too long to fit on one line in the Title of teh Report. Is there a way to split the line into 2 lines in the title. I have tried using unichar(10) for NEW LINE but no luck. help needed

  17. I can see, or not in this case, see how the user may not be able to easily identify the filters.
    I’m a total PBI novice. I’ve glanced at it a couple of times, but I’m still in the process of getting up to speed on PivotTables in Excel.

    I was looking for a way to make your card visuals more visible. Format them more like, but less bold, the row of text information above the.
    I’m sure it is brain dead simple, I just haven’t tripped over it yet.

  18. hi Reid! thank you for the great post!

    I tried to replicate it and i see a bizzare behavior when i add in the filters the Year. For a strange reason, i does not allow me to change the format of the year and i only managed to do so by going to the relationships view and changing the properties of the Selected Year measure:

    Year Selection = IF(ISFILTERED(‘Calendar'[Date].[Year]) , SELECTEDVALUE(‘Calendar'[Date].[Year] , “Multiple Years”), “No Selection”)

    here is a screenshot:

    any idea why the Measure Tools tab is grayed out for this item?

Leave a Reply

Your email address will not be published. Required fields are marked *