Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic Titles with Multiple Selections

I want to be able to do the following 

 

1. When no selection is made on a slicer: Return "All"

2. When 1-3 selections  are made on the slicer: Return "text1,text2,text3"

3. When 4 or more selections are on the slicer: Return "text1,text2,text3, ....."

 

The DAX below works for 2 and 3 but not 1. When no selection is made on the slicer i get "text1,text2,text3, ....." instead.

 

List of Lookup values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Column')
VAR __MAX_VALUES_TO_SHOW = 5
RETURN
IF(ISFILTERED(Column),
    IF(
        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    VALUES(Column),
                    Column,
                    ASC
                ),
                Column,
                ",",
                Column,
                ASC
            ),
            ", ..."
        ),
        CONCATENATEX(
            VALUES(Column),
            Column,
            ", ",
            Column,
            ASC
        )
    )
    , "All"
)

 

 

 

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    It works IMO.

     

    List of Lookup values =
    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sample'[Column])
    VAR __MAX_VALUES_TO_SHOW = 3
    RETURN
    IF(ISFILTERED('Sample'[Column]);
        IF(
            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
            CONCATENATE(
                CONCATENATEX(
                    TOPN(
                        __MAX_VALUES_TO_SHOW;
                        VALUES('Sample'[Column]);
                        'Sample'[Column];
                        ASC
                    );
                    'Sample'[Column];
                    ", ";
                    'Sample'[Column];
                    ASC
                );
                "..."
            );
            CONCATENATEX(
                VALUES('Sample'[Column]);
                'Sample'[Column];
                ", ";
                'Sample'[Column];
                ASC
            )
        );
        "All"
    )
     
    Scenario 1: No selection

     

    Scenario 2: 2 selection

    Scenario 3: More than 3

     

3 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    It works IMO.

     

    List of Lookup values =
    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Sample'[Column])
    VAR __MAX_VALUES_TO_SHOW = 3
    RETURN
    IF(ISFILTERED('Sample'[Column]);
        IF(
            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW;
            CONCATENATE(
                CONCATENATEX(
                    TOPN(
                        __MAX_VALUES_TO_SHOW;
                        VALUES('Sample'[Column]);
                        'Sample'[Column];
                        ASC
                    );
                    'Sample'[Column];
                    ", ";
                    'Sample'[Column];
                    ASC
                );
                "..."
            );
            CONCATENATEX(
                VALUES('Sample'[Column]);
                'Sample'[Column];
                ", ";
                'Sample'[Column];
                ASC
            )
        );
        "All"
    )
     
    Scenario 1: No selection

     

    Scenario 2: 2 selection

    Scenario 3: More than 3

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you Bolfri!

       

      looks like I had "Filters on all pages" which was preventing this to work on my end. 

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    Hi Anonymous ,

     

    Whether the advice given by bolfri  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


    Looking forward to your feedback.


    Best Regards,
    Henry