Forum Discussion
DAX Studio filter dump displays combined filtering
Good morning everyone!
I have a fun case here: i have made a filter dump from DAX Studio, which i then edited to not only show the filters that apply values, but also those that exclude values. Now i face the following problem: whenever i enable multiple filters based on columns from a singular table, the formula of DAX Studio does no longer display the filters applied on a column, but also recognizes that those columns filter each other and will display accordingly.
E.G.: When i filter 1 value out of Column A, the measure correctly displays '<> X'. But whenever i apply another filter from the same table, it will affect the first filter and the display no longer shows which filters are applied, but what gets crossfiltered also.
Now i have been puzzeling with ALL, ALLEXCEPT, ALLCROSSFILTERED, FILTERS etc., but i cannot find a solution. The desired result is that the measure ONLY dispays what is enabled or disabled in a column filter and ignores any filtering from other columns.
Sample code that i have been working in:
VAR MaxFilters = 3
VAR Logic =
IF (
ISFILTERED ('v_slm_0003_dash_202203110926'[PA]),
VAR ___f = FILTERS( v_slm_0003_dash_202203110926[PA] )
VAR ___r = COUNTROWS ( ___f )
VAR ___t = TOPN ( MaxFilters, ___f, 'v_slm_0003_dash_202203110926'[PA])
VAR ___d = CONCATENATEX ( ___t, 'v_slm_0003_dash_202203110926'[PA], "," )
VAR ___x = "[PA] = " & ___d & IF(___r > MaxFilters, ", ... [" & ___r & " items selected]") & ""
VAR ___nf =
FILTER(
ALL( 'v_slm_0003_dash_202203110926'[PA])
, NOT( 'v_slm_0003_dash_202203110926'[PA] ) IN ___f
)
VAR ___nr = COUNTROWS ( ___nf )
VAR ___nt = TOPN ( MaxFilters, ___nf, 'v_slm_0003_dash_202203110926'[PA])
VAR ___nd = CONCATENATEX ( ___nt, 'v_slm_0003_dash_202203110926'[PA], ", " )
VAR ___nx = "[PA] <> " & ___nd & IF(___nr > MaxFilters, ", ... [" & ___nr & " items selected]") & ""
RETURN
___r
, BLANK()
)
RETURN
Logic
The solution should be in VAR ___f.
Sadly i cannot supply you of any sample data, but this should be easily replicated with any existing datasets.
Thanks in advance!
2 Replies
- lbendlin
Super User
If you want our assistance please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - AnonymousNot applicable
Hi Anonymous,
Perhaps you can take a look at the following blogs about ISFILTERED, ISCROSSFILTERED and ALL functions if helps:
ISFILTERED, ISCROSSFILTERED – DAX Guide - SQLBI
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI
Regards,
Xiaoxin Sheng