Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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!

 

 

Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    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's avatar
    Pragati11
    6 years ago

    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

5 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Pragati11
        Super User
        6 years ago

        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