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 All,
I'm looking for a solution to provide results (numbers with 2 letters upfront known as ID).
I have 2 querries with tables containing different rows with one common row (ID).
I want ID's from one querry to be checked against ID's from the 2nd querry and the calculation to show only ID's that aren't in the 2nd (filtering out the ID's common to both)
I'm not sure what would be the best way of going about it. Any help will be greatly appreciated.
Solved! Go to Solution.
You can use EXCEPT dax fx
new calculated table = Except(Querry1, Querry2)
you'll get records form query 1 that are not preset in query 2.
reverse the query names in dax if you want to invert te result.
Hi @BobJohnson ,
I created some data:
Query1:
Query2:
Here are the steps you can follow:
1. Create calculated column.
Column =
var _ID=SELECTCOLUMNS('Query2',"1",[ID])
return
SUMX(FILTER(ALL(Query1),
NOT('Query1'[ID]) in _ID),[Value])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @BobJohnson ,
I created some data:
Query1:
Query2:
Here are the steps you can follow:
1. Create calculated column.
Column =
var _ID=SELECTCOLUMNS('Query2',"1",[ID])
return
SUMX(FILTER(ALL(Query1),
NOT('Query1'[ID]) in _ID),[Value])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
You can use EXCEPT dax fx
new calculated table = Except(Querry1, Querry2)
you'll get records form query 1 that are not preset in query 2.
reverse the query names in dax if you want to invert te result.
I think this should work however, what i didn't mention is one of the querries has more rows than the other. In which case the table cannot be completed by PowerBI.
Is there a way I can merge both querries to help rectify this?
use union
=UNION(Querry1, Querry2)
I dont understand wht you mean by table cannot be completed by powerbi.
Querry 1 has many more columns (i have said rows previously) and it brings the below error:
in case you haven't fugured it out yet, you can use Power Query to avoid writing complex DAX for above scenario. use Left Anti Join or Right Anti Join. If you have solved it, please share how you did it.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |