topic Re: Conditional Title using Quick measure calculation 'Concatenated list of values' in DAX Commands and Tips

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

Anonymous — Thu, 20 Feb 2020 11:51:32 GMT

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!

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

Pragati11 — Thu, 20 Feb 2020 12:09:49 GMT

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

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

Anonymous — Thu, 20 Feb 2020 12:29:38 GMT

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:

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

Does this help you?

Thank you for your help!

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

Pragati11 — Thu, 20 Feb 2020 13:34:49 GMT

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

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

Anonymous — Fri, 21 Feb 2020 15:47:17 GMT

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.

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

Pragati11 — Fri, 21 Feb 2020 15:55:25 GMT

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