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

[JustinDoh1]

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.

[shafiz_p]

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

[tharunkumarRTK · Accepted solution]

@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!

[JustinDoh1]

@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.