Can DAX recognize filters applied with Slicers? What about overall Page Filters?

[Anonymous]

Good morning everyone,

My question today is if there are any DAX functions or operators that can recognize the filters used within Slicers? Additionally, while on the topic and in case it is different, are there any that recognize the filters used in general on the current page?

Let me explain my situation, I have a KPI visualization on a page that compiles the data from different categories. The KPI is displaying a measure that I created and it correctly displays the KPI for that combination of categories. I then have a slicer on that page that allows me to filter through the different categories that make up that compiled measurement. However, when I use any filter on the slicer the KPI measurement becomes incorrect, obviously because I am not accounting for that filter selection in my measure's DAX. 

So I am thinking I need an IF or a SWITCH but I do not know what I can use to check for the "active filter" on the slicer so that my KPI can be calculated accordingly. I would really appreaciate any advice.

Thank you.

[Anonymous]

@Anonymous 

I got it! The solution is the following...

KPI = IF( COUNTROWS( DISTINCT( TableName[ColumnName])) = 1,

SWITCH( DISTINCT( TableName[ColumnName]),

Category1, KPI1,
Category2, KPI2,

Category3, KPI3,

Category4, KPI4,

Category5, KPI5,

Category6, KPI6,

N/A),

OverallKPI)

[Anonymous]

@Anonymous 

There's a specialized function that does what you do on the first line: HASONEVALUE. It's shorter and you should use it instead. Checking the condition GUARANTEES that you have one value returned from DISTINCT. This is what I meant.

[Anonymous · Accepted solution]

I see! It works. Thank you for that advice. In that case, the final solution is...

KPI = IF( HASONEVALUE(TableName[ColumnName]),

SWITCH( DISTINCT( TableName[ColumnName]),

Category1, KPI1,
Category2, KPI2,

Category3, KPI3,

Category4, KPI4,

Category5, KPI5,

Category6, KPI6,

N/A),

OverallKPI)

[Anonymous]

Here's a bit of your code:

SWITCH( DISTINCT( TableName[ColumnName]),...

Since you are not guaranteeing anyhere in the code that the table function DISTINCT returns only one row and one column (which is automatically converted by DAX behind the scenes into a single value), you are getting an error and the SWITCH is obviously wrong since it's not working in all circumstances. SWITCH has 2 versions: one for values and one for logical expressions. I was referring to the latter but I can see you're trying to use it for values. That's all good and well as long as you can guarantee that only ever one value will be returned by DISTINCT. You're not doing that.

I hope you'll fix the SWITCHes and it'll do what you want.

[Anonymous]

I see what you are saying now. I thought you meant that the entire SWITCH was wrong but you were just talking about the error that I, myself, had already also pointed out. The fact that it does not work when there is more than one value. ergo when a filter isnt active.

I dont really know how to "guarantee that only ever one value will be returned". I understand the issue, when there is no filter active DISTINCT is returning more than 1 value. Past that, i'm not too sure how to proceed. I'm thinking using COUNTROWS with the IF statement and only letting it go to the SWITCH if there is exactly 1 value. Otherwise, it defaults to the overall KPI value. 

[Anonymous]

@Anonymous 

Both SWITCHes are wrong. The first expression under SWITCH must be a logical expression - HOW TO CORRECTLY USE SWITCH. DISTINCT(...) returns a table, not something that's TRUE or FALSE. The second one is a mistake that many starting programmers make: ISFILTERED( ... ) is already a logical expression and does not need to be compared against a value, not to mention a string...

The error you get is a direct consequence of not using the function properly.

[Anonymous]

The SWITCHes are not wrong, they are working when any of the 6 filters in the slicer are active. I also never said anything about DISTINCT returning TRUE or FALSE. So i'd say you need to reread my reply because there was a misunderstading clearly.

What I did get wrong is comparing ISFILTERED agaisnt a value. Thanks for that comment. I'll fix that and see if it works.

[Anonymous]

The function VALUES or DISTINCT returns the currently visible values in a table. The values are there because either there is a direct filter on the column(s) or the values are there because the table is cross-filtered, or because there's no filter at all. If you want to know if there are any direct filters on a column of a table, then you can use the function ISFILTERED. There's also another sister function ISCROSSFILTERED and it checks if a column/table is cross-filtered. But be careful, though, a table that has a direct filter on it is also cross-filtered but not the other way round. Please check the documentation DAX Gude - ISCROSSFILTERED and DAX Guide - ISFILTERED, and DAX Guide - HASONEVALUE, DAX Guide - HASONEFILTER.

[Anonymous]

Thanks for the reply. VALUES and/or DISTINCT are what I needed, they help to an extent. So right now I am doing a SWITCH and if I filter by one of the 6 categories, it works. However, now the compiled does not work. Giving the following error...

"MdxScript(Model) (256, 9) Calculation error in measure 'TableName'[KPI]: A table of multiple values was supplied where a single value was expected."

I understand why it is happening. It is because without the filter DISTINCT is returning 6 unique values. So this was the first attempt that gave that error...

KPI = SWITCH( DISTINCT( TableName[ColumnName]),

Category1, KPI1,
Category2, KPI2,

Category3, KPI3,

Category4, KPI4,

Category5, KPI5,

Category6, KPI6,

OverallKPI)

So I need to figure out a way to handle the "Else" case. That is the case where none of the individual filters are applied and the Overall KPI should display.

I thought of wrapping the above SWITCH with an IF statement and using ISFILTERED but it either doesn't work for this purpose or I am doing something wrong. Something like...

IF( ISFILTERED( TableName[ColumnName]) = "true",

SWITCH( DISTINCT( TableName[ColumnName]),

Category1, KPI1,
Category2, KPI2,

Category3, KPI3,

Category4, KPI4,

Category5, KPI5,

Category6, KPI6,

0),

OverallKPI)

Does that logic make sense? Could you please point me in the right direction?