topic Re: How to modify this to show all values when filter selects ALL (Part 2) in DAX Commands and Tips

How to modify this to show all values when filter selects ALL (Part 2)

JustinDoh1 — Sat, 28 Dec 2024 23:47:50 GMT

I have posted this question about a week ago, and I thought my manager said "it is good" back then, but yesterday I heard that I need to come up with a solution as below:

Here is my latest PBIX file.

But, now bottom is requirement:

This is my previous post , and with following DAX code, I thought it would satisify the requirement from my manager:

ALI1 = IF ( ISFILTERED ( 'Z-Table'[Location] ), IF ( CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "ALI" ), "ALI", "" ), "" )

FILTER = IF ( ISFILTERED ( 'Z-Table'[Location] ), IF ( MAX ( 'tblScore'[Location] ) IN VALUES ( 'Z-Table'[Location] ), 1, 0 ), 0 )

Basically, current DAX works if user selects each selection one by one, but not when selected "Select all".

So, I want those characters ("ALI", "ARB" & "AVI") show up when selected individually as well as selected "Select all".

Is it possible?

Thanks.

Re: How to modify this to show all values when filter selects ALL (Part 2)

tharunkumarRTK — Sun, 29 Dec 2024 03:38:51 GMT

JustinDoh1

You just need to remove the ISFILTERED condition.

ALI1 = IF ( CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "ALI" ), "ALI", "" ) ARB1 = IF ( CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "ARB" ), "ARB", "" ) AVI1 = IF ( CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "AVI" ), "AVI", "" ) FILTER = IF ( MAX ( 'tblScore'[Location] ) IN VALUES ( 'Z-Table'[Location] ), 1, 0 )

If you want the table visual to show up all the characters if "select all" selected then remove the isfiltered condition. Else keep it as is.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Re: How to modify this to show all values when filter selects ALL (Part 2)

shafiz_p — Sun, 29 Dec 2024 06:12:57 GMT

Hi JustinDoh1 , When "Select All" is selected, it doesn't actually apply a filter. Instead, it removes all filters, which can make it seem like no specific value is selected. ISFILTERED() function returns FALSE when "Select All" is used because technically, no filter is applied.

However, I would like to suggest you to create a new column in Z-Table named "SelectAll". See image below:

This is for hirarchy. Now create a slicer with the hirarchy. See image below:

Now update the measures, you have created earlier with the following codes:

ALI1 = IF( NOT ISFILTERED('Z-Table'[SelectAll]), "", IF ( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && SELECTEDVALUE('Z-Table'[Location]) = "ALI", "ALI" , IF( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "ALI" ), "ALI", "" ) ) )

ARB1 = IF( NOT ISFILTERED('Z-Table'[SelectAll]), "", IF ( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && SELECTEDVALUE('Z-Table'[Location]) = "ARB" , "ARB" , IF( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "ARB" ), "ARB", "" ) ) )

AVI1 = IF( NOT ISFILTERED('Z-Table'[SelectAll]), "", IF ( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && SELECTEDVALUE('Z-Table'[Location]) = "AVI", "AVI" , IF( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All" && CONTAINSSTRING ( CONCATENATEX ( 'Z-Table', 'Z-Table'[Location], "," ), "AVI" ), "AVI", "" ) ) )

FILTER = IF ( NOT ISFILTERED('Z-Table'[Location]), IF(MAX('tblScore'[Location]) IN VALUES('Z-Table'[Location]), 1, 0), IF ( SELECTEDVALUE('Z-Table'[SelectAll]) = "Select All", IF ( MAX('tblScore'[Location]) IN VALUES('Z-Table'[Location]), 1, 0 ), 0 ) )

And you are done. Check the outputs below, for your understanding:

1. When no selection:

2. When single selections:

3. When 'Select All' is selected :

Hope this helps!!

If this solved your problem, please accept it as a solution and kudos!!

Best Regards,
Shahariar Hafiz

Re: How to modify this to show all values when filter selects ALL (Part 2)

JustinDoh1 — Mon, 30 Dec 2024 02:05:45 GMT

tharunkumarRTK Thank you so much for your help. I am just curious why those extra IF conditions were there at the first place. I am just trying to figure our the logic.