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.
Hi, I was wondering if anyone can help me.
I have two tables that have columns with similar values. In the example below i have TableA & TableB. TableA is the main table containing all possible unique StoreName values. TableB is a table that can contain mutiple StoreName values but doesn't always have every value from TableA.
TableB gets filtered by slicers and what i need is whatever State & Category values are left in TableB to filter TableA State & Category and then return results frm TableA where the StoreName does not exist in TableB
Hope this makes sense
Solved! Go to Solution.
try with this measure and then using a filter > 0
Measure =
COUNTROWS (
CALCULATETABLE (
VALUES ( TableA[StoreName] ),
TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ),
EXCEPT ( VALUES ( TableA[StoreName] ), VALUES ( TableB[StoreName] ) )
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
HI, @Liaise
You can try this formula as below:
Measure2 = VAR _TABLE2=CALCULATETABLE(VALUES(TableB[StoreName]),ALLEXCEPT(TableB,TableB[Category])) RETURN VAR A=COUNTROWS ( CALCULATETABLE ( FILTER(VALUES ( TableA[StoreName] ),NOT TableA[StoreName] in _TABLE2), TREATAS ( VALUES ( TableB[State] ), TableA[State] ), TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ) )) RETURN VAR B=COUNTROWS(TableA) RETURN IF(ISFILTERED(TableB[Category])&&ISFILTERED(TableB[State]),A,B)
Result:
IF not select from slicer
then I filter them
here is pbix, please try it.
Best Regards,
Lin
HI, @Liaise
You can try this formula as below:
Measure2 = VAR _TABLE2=CALCULATETABLE(VALUES(TableB[StoreName]),ALLEXCEPT(TableB,TableB[Category])) RETURN VAR A=COUNTROWS ( CALCULATETABLE ( FILTER(VALUES ( TableA[StoreName] ),NOT TableA[StoreName] in _TABLE2), TREATAS ( VALUES ( TableB[State] ), TableA[State] ), TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ) )) RETURN VAR B=COUNTROWS(TableA) RETURN IF(ISFILTERED(TableB[Category])&&ISFILTERED(TableB[State]),A,B)
Result:
IF not select from slicer
then I filter them
here is pbix, please try it.
Best Regards,
Lin
Thank you for your help also. Your solution did work but required both filters to have a selection which is not always required in my situation. But still a great solution and appreciate it
try with this measure and then using a filter > 0
Measure =
COUNTROWS (
CALCULATETABLE (
VALUES ( TableA[StoreName] ),
TREATAS ( VALUES ( TableB[State] ), TableA[State] ),
TREATAS ( VALUES ( TableB[Category] ), TableA[Category] ),
EXCEPT ( VALUES ( TableA[StoreName] ), VALUES ( TableB[StoreName] ) )
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you so much for your help. Your solution did work. I had a relationship between the two tables and once i removed it then your DAX solution worked perfectly.
Hi this actual filters out all results when i try. If i take out the EXCEPT statement line i do get all stores that fall under the two filters which is great. But once i try to find the unmatched using EXCEPT i get no results.
ok.. could you post a more complete dataset? what visual are you building? maybe upload a pbix file?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering 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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |