Display a dynamic text value based on the selected values on two slicers

[sebastian09]

Hello everyone,

I'm trying to create a measure in Power Bi Desktop to display a text value based on the selected filter.

Take a look at the table:

Report Category	Report Category 2
Communication
Corporate strategy	Engineering
Corporate strategy	Procurement
ESC
Finance	Global Business Services
Finance	IT
Human Resources

Not every Report Category has a Report Category 2.

I would like to build a measure to display a text based on the four assumptions:

1. When I choose in slicer only Report Category without existing Report Category 2 for this Report Category (For example Report Category: Communication) the measure will display text: "Report Category & " Expense Report""
2. When I choose in slicer only Report Category with existing Report Category 2 for this Report Category (For example Report Category: Finance) the measure will display text: "Report Category & " Expense Report""
3. When I choose in slicer only Report Category 2 (For example Report Category 2: IT - there is only for Finance) the measure will display text: "Report Category 2 & " Expense Report""
4. When no selection on slicers, the measure will display text: " Global Expense Report"

I wrote this measure in DAX:

Report name display =
VAR SelectedCategory = SELECTEDVALUE('Sharepoint database'[Report Category])
VAR SelectedCategory2 = SELECTEDVALUE('Sharepoint database'[Report Category 2])
RETURN
IF(
ISBLANK(SelectedCategory),
"Global Expense report", 
IF(
ISBLANK(SelectedCategory2),
SelectedCategory & " Expense report",
SelectedCategory2 & " Expense report"
)
)

And it working only on the three assumptions:

2. When I choose in slicer only Report Category with existing Report Category 2 for this Report Category (For example Report Category: Finance) the measure will display text: "Report Category & " Expense Report"".

3. When I choose in slicer only Report Category 2 (For example Report Category 2: IT - there is only for Finance) the measure will display text: "Report Category 2 & " Expense Report"".

4. When no selection on slicers, the measure will display text: " Global Expense Report".

But not working for:

1. When I choose in slicer only Report Category without existing Report Category 2 for this Report Category (For example Report Category: Communication) the measure will display text: "Report Category & " Expense Report"".

Please check screenshots:

1. Selected Report Category: Communication: - there is a problem. Should be "Communications Expense report"

2. Selected Report Category: Finance

3. Selected Report Category 2: IT

4. No selection:

Many thanks for your help.

[Wilson_ · Accepted solution]

Hi Sebastian,

I think the base problem is you think SELECTEDVALUE does something different than what it actually does. It doesn't actually check that you've selected a value; it checks that there is only a single value for that column. This happens when you make a single selection in a slicer of course, but it's not the only way. (For more details, check out the documentation here.)

When you select Communications from Report Category, Report Category 2 is not blank. As you can see in your first screenshot, when you select Communications, Report Category 2 has only one value (""), therefore SelectedCategory2 is not blank, ISBLANK ( SelectedCategory2 ) returns FALSE and your measure returns SelectedCategory2 & " Expense report".

The DAX function it seems you are actually looking for is ISFILTERED. (I'm also using SWITCH instead of nested IF functions, for readability.)

Following the logic you laid out at the top of your post, what your logic flow in your measure should be is:

Report name display =
VAR SelectedCategory = SELECTEDVALUE ( 'Sharepoint database'[Report Category] )
VAR SelectedCategory2 = SELECTEDVALUE ( 'Sharepoint database'[Report Category 2] )
VAR Result =
SWITCH (
    TRUE(),
    ISFILTERED ( 'Sharepoint database'[Report Category] ), SelectedCategory & " Expense Report",
    ISFILTERED ( 'Sharepoint database'[Report Category 2] ), SelectedCategory2 & " Expense Report",
    "Global Expense Report"
)

RETURN Result

I would also recommend you set your slicers to single selections because I don't think you intend users to select more than one. Therefore, don't give them that option. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[sebastian09]

Hi Wilson,

thank you for your response.

I'm very grateful for your explanation about SELECTEDVALUE formula.

Albo, you have right about multiple selection on the slicer, bout please be informad, thet was only for preview, in the official version I used only single selection slicer.

Many thank you for your DAX measure. Everything working without issues.

Kind regards,

Sebastian

[Wilson_]

Sebastian,

Perfect, thanks for the update. Happy I could help. 😄