Forum Discussion
Cross filter ( to exclude )
I previously raised this query ( https://community.powerbi.com/t5/Desktop/Cross-filtering-across-tables/m-p/1398390# ) and (https://community.powerbi.com/t5/Desktop/Cross-Filter-across-tables-2/m-p/1412162#M597413) but haven't got effective response to close the issue. Reposting with more clarity on the requirement.
Restating the example below, with minor edit in filters :
TABLE 1
Each row shows 1 ID only. ID does not repeat in TABLE 1
Measure : Count_of_unique_ID = 8
TABLE 2
Each row is a unique combination of ID and Condition. i.e. ID,condition won't repeat. The table may have <= IDs from table 1. E.g. it will not have a new ID 10 which has not occured in table 1 and it doesn't need to have all IDs as they occur in table 1. Where there was no condition e.g. 4,6,8 , they didn't occur in table 1.
The current join is in BOTH direction and via ID
FILTERS & MEASURE - EXPECTED OUTPUT.
FILTER 1 = AGE < 35 [REMOVE ALL that are above 35]
FILTER 2 = EXCLUDE (not filter on) IDs with CONDITION (A OR B) - two selected [ multiple selection should be allowed here in solution]. All others which do not have condition A or B or any condition ( do not come in table 2 ) should be shown. Where A or B occured ( IDs , should be excluded ) . The filter should work as multi select e.g. (A or B)
TABLE 1 after filters
Measure : Count_of_unique_ID = 3
Based on previous solution by (Community Support Team _ zhenbw) in link (https://community.powerbi.com/t5/Desktop/Cross-filtering-across-tables/m-p/1398390#) it only works if one condition is selected i.e. A . If i select two conditions, it reverts to its original count.
What is the best appraoch to resolve this?
Hi pbhat89 ,
Not sure what you mean by "I read online that we cannot reference variables from a temp table in power BI." but check the post below by SQLBI where they make references to how to use variables calculations and get data from those tables.
https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/
Regarding my second comment I was refering that you can use the new M query parameters to make a new table that picks up the slicer values filtering your data and then use that 3rd table to make your data visualization in conjunction with the slicer of the condition.
Check the blog post below:
https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/
9 Replies
Hi pbhat89 ,
Based on the data you show you need to do a measure similar to this one:
Calculation = VAR temp_table = FILTER ( ALLSELECTED ( Table1[ID]; Table1[Age] ); NOT ( Table1[ID] IN VALUES ( Table2[ID] ) ) ) RETURN COUNTROWS ( temp_table )Be aware that I'm only working with two slicers on Age and on condition if you want to add additional slicers for height or score you need to add it to the allseleted stament.
Resulta attach and below:
Be aware that in this option I have turn off the interactions betweent the two slicers but they can be on if you need it.
Since in actual case - i have many variables in table 1 which may be used as filters - could you advise something like :
FILTER(input_db_disc_edited,input_db_disc_edited[name]=_selected)) + the NOT ( table1[ID]....in table2[ID])
