Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Pankhuri_16
Frequent Visitor

Dax measure help for IF else condition

Below is dax measure to return if anything is filtered from the slicers . I have to add an else condition if nothing is selected. Need assistance to add an else condition as indivually if I add else in all if statements, it gives me multiple else condition values.

Applied_Filters =

VAR MaxFilters = 3
RETURN
IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] )
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date], ", " )
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] )
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day], ", " )
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] )
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month], ", " )
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

To add an "else" condition in your DAX measure for the scenario when nothing is selected in any of the slicers, you can use an additional IF statement outside of the existing ones. Here's how you can modify your measure to include the "else" condition:

 

Applied_Filters =
VAR MaxFilters = 3
RETURN
IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
NOT (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ) ||
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ) ||
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] )
),
"No filters applied",
BLANK()
)

 

This additional IF statement checks if none of the slicers are filtered. If none of them are filtered, it returns the string "No filters applied". Otherwise, it returns a blank value. This ensures that you get a meaningful result even when no filters are selected.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

 

View solution in original post

2 REPLIES 2
Pankhuri_16
Frequent Visitor

Thanks a ton! It worked!

123abc
Community Champion
Community Champion

To add an "else" condition in your DAX measure for the scenario when nothing is selected in any of the slicers, you can use an additional IF statement outside of the existing ones. Here's how you can modify your measure to include the "else" condition:

 

Applied_Filters =
VAR MaxFilters = 3
RETURN
IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___f = FILTERS ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___r = COUNTROWS ( ___f ),
VAR ___t = TOPN ( MaxFilters, ___f, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] ),
VAR ___d = CONCATENATEX ( ___t, 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month], ", " ),
VAR ___x = "'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " others]") & " "
RETURN ___x & UNICHAR(13) & UNICHAR(10)
)
& IF (
NOT (
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Date] ) ||
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Day] ) ||
ISFILTERED ( 'DateTableTemplate_3c7006c1-cdf4-4313-9ad9-0bc2cfd685d9'[Month] )
),
"No filters applied",
BLANK()
)

 

This additional IF statement checks if none of the slicers are filtered. If none of them are filtered, it returns the string "No filters applied". Otherwise, it returns a blank value. This ensures that you get a meaningful result even when no filters are selected.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors