Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I would like to use DAX to insert a header which shows what filter selections have been made. It need to show "Level (Group): " followed by the filter selection. If there's more than one selection then to show them all separated by ", " and if there are no filter selections then to just show the following text, "Level (Group): All".
My DAX is...
Any tips would be gratefully received.
Best,
DHB
Solved! Go to Solution.
Give this a try
Commencing Header =
IF (
ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
VAR __x = "Level (Group): " & __d
RETURN __x,
"Level (Group): All"
)
Taken from this article: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/
Yes, we can add a count of the total number of items and compare the selection to that.
Commencing Header =
IF (
ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
VAR __t = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Austrade (Append)'[Level (groups)] ), ALL ( 'Austrade (Append)' ) ) )
VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
VAR __x = IF ( COUNTROWS ( __f ) = __t, "Level (Group): All", "Level (Group): " & __d)
RETURN __x,
"Level (Group): All"
)
Yes, we can add a count of the total number of items and compare the selection to that.
Commencing Header =
IF (
ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
VAR __t = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'Austrade (Append)'[Level (groups)] ), ALL ( 'Austrade (Append)' ) ) )
VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
VAR __x = IF ( COUNTROWS ( __f ) = __t, "Level (Group): All", "Level (Group): " & __d)
RETURN __x,
"Level (Group): All"
)
Give this a try
Commencing Header =
IF (
ISFILTERED ( 'Austrade (Append)'[Level (groups)] ),
VAR __f = FILTERS ( 'Austrade (Append)'[Level (groups)] )
VAR __d = CONCATENATEX ( __f, 'Austrade (Append)'[Level (groups)], ", " )
VAR __x = "Level (Group): " & __d
RETURN __x,
"Level (Group): All"
)
Taken from this article: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/
That's perfect, thank you @jdbuchanan71 . If someone manually clicked all 5 to get all, rather than not selecting any, is there something I could add to that code to make it read "Level (Group): All" instead of getting a concatented list of all 5?
Check if you only have one value and then return that one value, if more than one value use the following.
CONCATENATEX('Austrade(Append)', 'Austrade(Append)'[Level (Groups)], ",")
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
90 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |