Error message on Card based on multiple filter selection

DAX DAX Calculations
1

Good Day,

I would greatly appreciate some assistance on the following:

I would like to develop a card visual in PowerBI that shows an error message should the following instance occur:

Say I have two dropdown filters:

Filter 1 - Site
Filter 2 - Scenario

For every site that is selected in the dropdown, only one scenario should be allowed per site.

This means if I select in filter 1:

Site A
Site B

Now in filter two (which is the Scenario filter), I will see all scenarios belonging to Site A and Site B, and should I select more than one scenario belonging to Site A and more than one scenario belonging to site B - the card visual should display an error message saying:

“Incorrect selection, please select one scenario per site”

2

@Akshay_05,

See if this works for you:

Card Display Scenario = 
IF(
    COUNTROWS( VALUES( Data[Scenario]  )) > 1,
    "Incorrect selection, please select one value per site",
    VALUES( Data[Scenario] )
)

image

I hope this is helpful. Full solution file attached.

3

Hi @BrianJ,

Thanks for the prompt response,

However, this is still not working as I want,

I am selecting multiple sites in Filter 1,

and selected only one Scenario per site in Filter 2 - this should be possible, however it is showing me

“Incorrect selection, please select one value per site”

Alternatively, if I select two Scenarios from the same Site, then it should display the error message:
“Incorrect selection, please select one scenario per site”

5

@Akshay_05,

Can you please post your PBIX, since in my file it seems to be working fine per your requirements:

image
image799Ă—155 4.21 KB

image
image817Ă—194 8.45 KB

Thanks.

  • Brian
6

Hi @BrianJ,

I developed a mock PowerBI report (see attached).

I have created 2 sheets (Case 1 and Case 2).

If you refer to the 1st sheet (Case 1) - I have selected two Scenario ID’s (Scenario ID 1 and 7), coming from 2 different Sites (Site 1 and Site 2). I need the card to display “Selection Correct” since this instance needs to be possible.

If you refer to the 2nd sheet (Case 2). I have made the same selection as represented in Case 1 , however, I have selected two Scenario IDs coming from Site 1(Scenario ID 1 and 2) and for Site 2 we have one Scenario ID (Scenario ID 7), and due to this it needs to display the error:

“Incorrect selection, please select one value per site” - because we have 2 Scenario IDs selected for Site 1

It is only possible to select one Scenario ID from one site at any given time. (Note - each Site has multiple Scenario ID’s)

I hope I have explained it better. Thanks for the assistance

DNA Enterprise - Card Display Scenario.pbix (17.5 KB)

7

@Akshay_05,

OK, this takes two more complex measures:

Count Scenarios Per Site = 

CALCULATE(
    COUNTROWS( Data ),
    ALLSELECTED( Data[Site ] ),
    VALUES( Data[Site ] )
) 

Card Display Scenario = 

VAR vTable =
ADDCOLUMNS(
    VALUES( Data[Site ] ),
    "@ScenCount", [Count Scenarios Per Site]
)

VAR Result =
    IF(
        MAXX(
            vTable,
            [@ScenCount]
        ) > 1,
        "Incorrect selection, please select one value per site",
        CONCATENATEX(
            VALUES( Data ),
            Data[Site ] &": "  &Data[Scenario ID],
            UNICHAR(10),
            Data[Site ],
            ASC
        )
    )

RETURN
Result

image
image811Ă—523 30 KB

image
image832Ă—527 23.6 KB

I hope this meets your requirements. Revised solution file attached.

2 Likes
8

Hi @BrianJ,

You are a Legend! It works like a GEM!

Thank you very much!

9

@Akshay_05,

My pleasure – glad that worked well for you. This was a fun one to work through, since I’ve never seen this exact scenario before.

  • Brian
1 Like