Forum Discussion
Cross filter ( to exclude )
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/
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])Hi pbhat89 ,
As refered you need to place those variable inside the allselected
Calculation = VAR temp_table = FILTER ( ALLSELECTED ( Table1[ID]; Table1[Age]; table [column];...;table [columnzz] ); NOT ( Table1[ID] IN VALUES ( Table2[ID] ) ) ) RETURN COUNTROWS ( temp_table )thanks MFelix - i did get it to work but with my actual dashboard but had to add if filtered argument to ensure apply not in only when any filters are selected in conditions table. my actual Data it gets far for more complex where i have many calculations apart from just count. These are conditional formulaes which make it difficult to write a code with requirements ( filtered and not in ) .
I simply want to get a table X ( subset of table 1 ) which updates as i
Part (A) : select the filters ( say Age , Height )
Part (B) : but has a different interaction (B) with table 2 -
- if table 2 is filtered ( one or more selected ) - then use the not in argument ( e.g. as shared by you earlier )
- if table has not been filtered ( nothing selected in conditions ) - table X should only show based on filters ( Age , Height ) and not
have any connection with Table 2.
Once this table X is generated / updated as i change parameters in part A or B - i can create all formulaes from this single table X. Its easy to get the filters part done (as the columns are within table 1) but with part B it becomes complex How can we create a dynamic table ( from queries ) which updates based on (A) and (B) above ?
