USING CONCATENATEX IN A DYNAMIC TITLE FOR MULTIPLE SLICERS

[Anonymous]

So I have a dynamic title which concatenates values which are selected from a slicer. If no slicer is selected, the title is blank. 

However - my DAX measure only accounts for a single slicer being filtered, and I have four.

The slicer is called 'UNDERWRITER' in a table called 'REVIEWS'

Here is my measure: 

FILTERED TITLE = IF(ISFILTERED('REVIEWS'[UNDERWRITER]),"ERROR SPLIT BY - "& CONCATENATEX(VALUES('REVIEWS'[UNDERWRITER]),'REVIEWS'[UNDERWRITER],", "),"")

In addition to 'UNDERWRITER', there are three other slicers from the 'REVIEWS' table that my users can choose from. Call them 'SLICER_A'  'SLICER_B'  'SLICER_C' 

Can I incorporate these into the same measure? 

[parry2k]

@Anonymous glad it worked out and you learned something new., Feel free to subscribe to my YouTube channel, I post interesting (corner case) Power BI solution/tricks. Cheers!!

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous and the reason for that is because all the slicers are from the same table if you have a separate dimension it would have been easier. Check this video on my YT channel and see if that helps and take you out from the mess 😁

https://youtu.be/CiqxnAGvv-c 

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k I get it. That's a pain, I see why it's doing it - but I think DAX should allow you to specify because 9/10 you're not going to want to display EVERYTHING if you've just filtered one thing. 

Thanks so much for your help on this, I have learnt a lot!

[parry2k · Accepted solution]

@Anonymous if you have formatted the code correctly you could have figured it out. You were almost there:

FILTERED TITLE =
IF (
    ISFILTERED ( 'REVIEWS'[UNDERWRITER] )
        || ISFILTERED ( 'REVIEWS'[NEW BUSINESS/RENEWAL] )
        || ISFILTERED ( 'REVIEWS'[CLAIM BASIS] )
        || ISFILTERED ( 'REVIEWS'[REGION] ),
    "ERROR SPLIT FOR - "
        & CONCATENATEX ( VALUES ( 'REVIEWS'[UNDERWRITER] ), 'REVIEWS'[UNDERWRITER], ", " )
        & CONCATENATEX (
            VALUES ( 'REVIEWS'[NEW BUSINESS/RENEWAL] ),
            'REVIEWS'[NEW BUSINESS/RENEWAL],
            ", "
        )
        & CONCATENATEX ( VALUES ( 'REVIEWS'[CLAIM BASIS] ), 'REVIEWS'[CLAIM BASIS], ", " )
        & CONCATENATEX ( VALUES ( 'REVIEWS'[REGION] ), 'REVIEWS'[REGION], ", ", "" )
)

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k yes you're totally right. I see what I did wrong. I should have laid it out as you had, then I would have reaslised. 
What I have discovered now though, is that even if you select a single slicer, the measure is displaying every single option available from all the other slicers. For example if I select a region, the measure displays that region, plus all the underwriters that fall under that region - even if I haven't filtered 'UNDERWRITER'. It's a hot mess. Is there any way to tell the measure to only display the results that have been selected? 

I tried changing my measure to 'ISCROSSFILTERED' but that doesn't affect the result.

[mahoneypat]

You should be able to create a string for each slicer's values in separate variables (using same approach as you've shown), and then concatenate them with "&" in your Return.

Pat

[Anonymous]

@mahoneypat I wish I was clever enough to figure out how to do this!

I tried the following but it stil only shows me the underwriter name, and the rest is just commas:

FILTERED TITLE = IF(ISFILTERED('REVIEWS'[UNDERWRITER])||ISFILTERED('REVIEWS'[NEW BUSINESS/RENEWAL])||ISFILTERED('REVIEWS'[CLAIM BASIS])||ISFILTERED('REVIEWS'[REGION]),"ERROR SPLIT FOR - "& CONCATENATEX(VALUES('REVIEWS'[UNDERWRITER]),'REVIEWS'[UNDERWRITER],", ")&CONCATENATEX(VALUES('REVIEWS'[NEW BUSINESS/RENEWAL]),", ")&CONCATENATEX(VALUES('REVIEWS'[CLAIM BASIS]),", ")&CONCATENATEX(VALUES('REVIEWS'[REGION]),", ",""))

[amitchandak]

@Anonymous ,  Try like

FILTERED TITLE = IF(ISFILTERED('REVIEWS'[UNDERWRITER]) || ISFILTERED('REVIEWS'[COLUMN A]) || ISFILTERED('REVIEWS'[COLUMN B])|| ISFILTERED('REVIEWS'[COLUMN C]) ,"ERROR SPLIT BY - "& CONCATENATEX(VALUES('REVIEWS'[UNDERWRITER]),'REVIEWS'[UNDERWRITER],", "),"")

[Anonymous]

Thanks @amitchandak but this still only displays the underwriter in the dynamic title. I need it to display - concatenated based on how many are selected from the slicer - whatever the user has selected. I tried adding || and & to the end as well but it errors.