Conditional Title using Quick measure calculation 'Concatenated list of values'

[Anonymous]

Hello all,

My goal is to use a Card-Visualization to list all selected filters from a slicer. For this I used a quick measure and selected 'Concatenated list of values'. While this gets me the result I want, I would prefer it, if each filter in the card would start on a new line. Additionally, I would like the card to just say 'All fields' when more than 5 filters are active.

I tried to apply these changes using DAX, but having no experience using it, I could not make it work only by looking it up on the internet.

List of Field values =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Planning'[Field])

VAR __MAX_VALUES_TO_SHOW = 5

RETURN

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW;

                    VALUES('Planning'[Field]);

                    'Planning'[Field];

                    ASC

                );

                'Planning'[Field];

                ", ";

                'Planning'[Field];

                ASC

            );

            ", etc."

        );

        CONCATENATEX(

            VALUES('Planning'[Field]);

            'Planning'[Field];

            " & ";

            'Planning'[Field];

            ASC

        )

    )

I appreciate your help, have a nice day!

[Pragati11 · Accepted solution]

Hi @Anonymous ,

I got this working by creating a measure as shown below:

new Check =

var total = CALCULATE(COUNT(ColorSample[color]), ALL(ColorSample[color]))

RETURN

IF(HASONEVALUE(ColorSample[color]), SELECTEDVALUE(ColorSample[color]), IF(COUNT(ColorSample[color]) > 1 && COUNT(ColorSample[color]) < total, CONCATENATEX(VALUES(ColorSample[color]), ColorSample[color], UNICHAR(10)) , "All Selected"))

NOTE: "ColorSample[color]" is my color columns with different color values. Replace this with your column.

Move this measure to your card visual. You get as follows:

1. when selected few values from slicer:

2. When everything is selected:

Hope this works for you.

If it works please mark this as a solution 🙂

Thanks,

Pragati

[Pragati11]

Hi @Anonymous ,

Can you add some screenshots on how this looks in Power BI report - your card visual with the slicer value selection?

Thanks,

Pragati

[Anonymous]

Hi @Pragati11  ,

Sure, I just came up with a quick example:

When only a few filters are selected it would show the card like this:

Card and Slicer (without word wrap)

Here I would prefer it, if every selected filter would start on a new line like this (in the card):

Black

Blue

Green

Grey

When all filters (or no filters) are selected it looks like below. In this case I'd like the card to say "All colors".

Card and slicer (all selected)

Does this help you?

Thank you for your help!

[Pragati11 · Accepted solution]

Hi @Anonymous ,

I got this working by creating a measure as shown below:

new Check =

var total = CALCULATE(COUNT(ColorSample[color]), ALL(ColorSample[color]))

RETURN

IF(HASONEVALUE(ColorSample[color]), SELECTEDVALUE(ColorSample[color]), IF(COUNT(ColorSample[color]) > 1 && COUNT(ColorSample[color]) < total, CONCATENATEX(VALUES(ColorSample[color]), ColorSample[color], UNICHAR(10)) , "All Selected"))

NOTE: "ColorSample[color]" is my color columns with different color values. Replace this with your column.

Move this measure to your card visual. You get as follows:

1. when selected few values from slicer:

2. When everything is selected:

Hope this works for you.

If it works please mark this as a solution 🙂

Thanks,

Pragati

[Anonymous]

Thank you very much for your help, @Pragati11  !

I copied the measure and it works great. Would the measure need to be change lot, if I also would like to have have a comma (or &) after each selected filter, except the last?

Yellow,

Orange,

Red

By the way, would you recommend any websites or other resources for learning about Power BI or DAX?

Thanks again.

[Pragati11 · Accepted solution]

HI @Anonymous 

Just modify the measure to the following:

new Check = var total = CALCULATE(COUNT(ColorSample[color]), ALL(ColorSample[color])) RETURN IF(HASONEVALUE(ColorSample[color]), SELECTEDVALUE(ColorSample[color]), IF(COUNT(ColorSample[color]) > 1 && COUNT(ColorSample[color]) < total, CONCATENATEX(VALUES(ColorSample[color]), CONCATENATE(ColorSample[color], ","), UNICHAR(10)) , "All Selected"))

This will work and give you desired output.

I generally use https://docs.microsoft.com/en-us/dax/ to refer to DAX functions in Power BI.

Give Kudos and accept as a solution! 🙂

Thanks,

Pragati