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.
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?
Solved! Go to Solution.
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/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSince in actual case - i have many variables in table 1 which may be used as filters - could you advise something like :
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 )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthanks @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 ?
Hi @pbhat89
If I understand you correctly what you need is if no values are selected in the slicer condition you should get 6 (with an age filter below 35) if values are selected you get the correct number:
Is this correct?
You just need to make the calculations based on your initial table in this case I'm using a count you must rewrite the formula to:
Calculation =
VAR temp_table =
FILTER (
ALLSELECTED ( Table1[ID]; Table1[Age] );
NOT ( Table1[ID] IN VALUES ( Table2[ID] ) )
)
RETURN
IF (
ISFILTERED ( Table2[Condition] );
COUNTROWS ( temp_table );
COUNTROWS ( Table1 )
)
Be aware that this selection ISFILTERED takes into account that you have any selection on the slicer condition so if you select A, B and C it will use the not if you deselect all of them it will use the table1.
Not sure if I have understood what you need but hope this is the result you need.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Your understanding is correct and i have managed to get my formulaes in the form of syntax below.
(ignore the naming as this is based on my actual data )
Hi @pbhat89 ,
The use of the temporary table gives you that need that you want that is to create the table with only a few rows and then make calculations on top of it using the different columns that are withing that temporary table.
When having more than one measure related to other the calculations can become pretty strange, especcially if you have IF statements on the return syntax, more when you change context on a visualization the results can be also messy.
You can check the new M query parameter update that is based on a slicer selection maybe you can create a new table to make your slicer and then work out the not in from your other table.
https://powerbi.microsoft.com/en-us/blog/power-bi-october-2020-feature-summary/#_Toc52195564
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
When i create to create a temp table - it does not allow me to reference the columns in the calculate part. I read online that we cannot reference variables from a temp table in power BI.
For your other comment : could you advise how to do so?
If i have a table :
how do i create a dynamic table which updates based on filters on table 1 , and selection of above leading to changes in table 2 --> and table 1 thereafter ( not in part )
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/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |