PowerBI : How to Display multiple selected values in a Card?

I recently worked upon a scenario where I have shown all the selected values in slicer in a Card. So, Card basically acts as a display screen which shows all the selected values in the slicer. The scenario which I just mentioned is very relevant and practical from reporting perspective with an intention to show selected values in a particular area of the report.

So, How to display multiple selected values in a Card? In order to display multiple selected values in a Card, you will need a conditional statement to check if any value in the slicer is selected by the user or not. If any value is selected then we need to capture those multiple or single selected values. If nothing is selected then we need to show the spaces at that place.

So, Now let`s go a little deeper and work on the actual use cases. For the practical use case purpose, I have used My favorite Sample Superstore dataset. If you do not have it you download it from here.

I have already mentioned the Problem statement and also provided it solution theoretically but it does not solve the purpose until I describe the DAX Expression I have used for solving the problem statement.

So let`s know about the DAX function required for displaying the multiple selected values in a Card.

ISFILTERED is a boolean DAX function which returns TRUE when Column Name is filtered and returns FALSE when the Column Name is not filtered.

Syntax : ISFILTERED(Column Name)

IF is a conditional statement. Its first argument is the Condition. If the condition is met then expression returns the second argument and If the condition is not met then the third condition is returned.

Syntax: IF(<Condition>,Value if condition is TRUE, Value if condition is FALSE)

CONCATENATX concatenates the result of an expression evaluated for each row in a table.

Syntax : CONCATENATX (<table>, <expression>, [delimiter])

<Table>: Table for which expression will be evaluated.
[delimiter] : (Optional) Delimiter will be used to separate the Expression Result.

<Expression>: The Expression will be evaluated for each row of the table.

ALLSELECTED is a function from ALL Family and used to remove any filter other than the table under its scope.

Syntax: ALLSELECTED(Table)

So, Now let`s come back to our practical use case which we need to solve.

Practical Use Case

Here, We have placed two slicers first one on the City Column and another on the Category Column and later we want to see the selected value in the Card.

Solution

We need a measure which will be placed on the Card. So let’s see the DAX Expression which we need to create.

show_selected_value =

IF (
    ISFILTERED ( Orders[Category] ),
    “Category : “
        CONCATENATEX (
            ALLSELECTED ( Orders[Category] ),
            UNICHAR ( 9899 ) & Orders[Category],
            “,”
        ),
    ” “
)
    IF (
        ISFILTERED ( Orders[City] ),
        ”    City : “
            CONCATENATEX (
                ALLSELECTED ( Orders[City] ),
                UNICHAR ( 9899 ) & Orders[City],
                “,”
            ),
        ” “
    )

I have already discussed individually each of the DAX functions used in the above expression. Now, let`s see what does the measure does in the Card. Let`s discuss the first part of the show_selected_value measure.

IF (
    ISFILTERED ( Orders[Category] ),
    “Category : “
        CONCATENATEX (
            ALLSELECTED ( Orders[Category] ),
            UNICHAR ( 9899 ) & Orders[Category],
            “,”
        ),
    ” “
)

Here, the IF condition is evaluating the whether Category column is filtered or not. If Category Column is filtered then below expression is getting evaluated.

“Category : “
        CONCATENATEX (
            ALLSELECTED ( Orders[Category] ),
            UNICHAR ( 9899 ) & Orders[Category],
            “,”

“Category:” is the string which gets combined with the output of Concatenatex Expression, All the multiple selected values in the Category Slicer is evaluated and concatenated using delimiter “,” UNICHAR(9899)  gives a circular symbol which will be concatenated at the beginning of each row value of the category Column.

The same operation happens for the below mentioned DAX Expression.

IF (
        ISFILTERED ( Orders[City] ),
        ”    City : “
            CONCATENATEX (
                ALLSELECTED ( Orders[City] ),
                UNICHAR ( 9899 ) & Orders[City],
                “,”
            ),
        ” “

    )

In the above expression, the Same operation is performed as it is done for the Category Column but here evaluation is done on City Column for which City Slicer has also been placed.


Final Thoughts


Displaying multiple selected values in a Card was used in one of the reports I have worked upon and I believe it is a quite common scenario that you can practice and improve your DAX skills. I hope that you found the article useful. Please do let me know your feedback so that I can help you more with your journey in learning PowerBI.


Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *