IF AND ISBLANK statement

[Anonymous]

Hi, 

I want to inform users to select values from all slicers and if the user skips a selection, want to display the message otherwise blank. I created the following DAX but it gave me blank even when just only slicer is selected.

Lablel =
VAR programselect =
    SELECTEDVALUE ( Data[Program] )
VAR schoolselect =
    SELECTEDVALUE ( Data[School] )
VAR collegeslect =
    SELECTEDVALUE ( Data[College] )
RETURN
    IF (
        ISBLANK ( programselect ) &&ISBLANK ( schoolselect ) && ISBLANK ( schoolselect ,
        "Select a Program and affiliated School and College from the slicers",
        ""
    )

Thanks for your time and support

[PBFlash]

Shouldn't the IF logic be using OR istead of AND?

Lablel =
VAR programselect =
    SELECTEDVALUE ( Data[Program] )
VAR schoolselect =
    SELECTEDVALUE ( Data[School] )
VAR collegeslect =
    SELECTEDVALUE ( Data[College] )
RETURN
    IF (
        ISBLANK ( programselect ) || ISBLANK ( schoolselect ) || ISBLANK ( schoolselect) ,
        "Select a Program and affiliated School and College from the slicers",
        ""
    )

[Anonymous]

Hi @PBFlash , Thanks for the response.

I want the selection from all three slicers if anyone is skipped, the want to have a message for the selection otherwise blank.  So IF logic with AND should make sense, but nevertheless I tried with OR also, and it gave the blank when one slicer is selected.

[MFelix]

Hi @Anonymous ,

Believe that the solution provided by @PBFlash  is correct, but I believe that your calculations are made based on a single table what can be happening is that when you select one slicer all others are filter to only have one value so the result is blank on your visual.

Assume this example data:

School	Program	Colleage	Value
1	1	1	a
2	1	1	c
2	2	2	d
3	1	1	a
3	2	2	e
4	1	1	d
4	2	2	a

Since School one only has one value when I select that school from the slicer automatically the other two slicers have only one value and the measure is blank and no error is given.

Can you please confirm that with your data?

Regards,

MFelix

[Anonymous]

Hi @MFelix  and @PBFlash 

Thanks for your time and guidance.

As @MFelix figured out correctly, the slicers are coming from the same table and selection of one value filters other values somehow too. But the tricky part is that  I have to manually select the values of other slicers in order for my other measures to work correctly.

[MFelix]

Hi @Anonymous ,

Based on your data and since you have a single program per centre / school if the selection is based on the program first you will always get theblank value without selecting the other slicers.

If the measure are based on the slicer they should work even if people only select one slicer. You can had a filter context to the measure to get the program maybe.

How are you measure setup?

Regards,

MFelix

[Anonymous]

Hi @MFelix ,

Some of the measures are as per the below beside the measures that you helped me with for Above and Below Average:

pro_school = CALCULATE(DISTINCTCOUNT(Sheet1[program]),ALLEXCEPT(Sheet1,Sheet1[centre],Sheet1[school]))

application = DISTINCTCOUNT(Sheet1[transaction])

app_school = CALCULATE([application],ALLEXCEPT(Sheet1,Sheet1[centre],Sheet1[school]))

ave_school = [app_school]/[pro_school]

Aplications Below = 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] < [AppAverageSchool]
    );
    [ApplicationCount]
)

Aplications Above= 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool]
    );
    [ApplicationCount]
)

[MFelix · Accepted solution]

Hi @Anonymous .

Looking at your measure they should work no mater what is the selection based on your slicers.

One option you could do is use a custom visual for slicer.

Check the Hierarchy Slicer on the windows store:

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380820?tab=Overview

This will give you the selection of the 3 options if you add them to the slicer.

Regards,

MFelix

[Anonymous]

Hi @MFelix  , thanks again for the support. I used the hierarchy slicer earlier, and it is a good option but unfortunately, the end-user did not like it.