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.
Dear All,
I have the following two tables:
TABLE1
CompanYName1 | Amount€ |
ALPHA LLC | 5.000 |
ACME INC | 1.000 |
SOYLENT GREEN CORP | 3.000 |
SLUDO LLC | 4.000 |
XXX A SEXY HORROR COMPANY | 6.000 |
TABLE2
CompanYName2 |
ALPHA LLC |
ACME INC |
SOYLENT GREEN CORP |
Those two tables are linked through a relationship.
I have a measure that is: [TABLE1]SUM(Amount€) = 19.000
I would like to create a measure which gives me back 10.000 which is the amount of the companies listed in TABLE2 which are not listed in TABLE1.
Thank you a lot for your time
Best regards
gianmarco
Solved! Go to Solution.
@gianmarco It should be if you use the same "__Company" column name. That's the reason to use SELECTCOLUMNS. I mocked it up and it seems to work. See attached PBIX below signature.
Hi @gianmarco ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _table2=SELECTCOLUMNS('Table2',"1",[CompanYName2])
return
SUMX(FILTER(ALL(Table1),
NOT('Table1'[CompanYName1]) in _table2),[Amount€])
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 @gianmarco ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _table2=SELECTCOLUMNS('Table2',"1",[CompanYName2])
return
SUMX(FILTER(ALL(Table1),
NOT('Table1'[CompanYName1]) in _table2),[Amount€])
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
@gianmarco Try:
Measure =
VAR __Table1 = SELECTCOLUMNS('TABLE1',"__Company",[CompanyYName1])
VAR __Table2 = SELECTCOLUMNS('TABLE2',"__Company",[CompanyYName1])
VAR __Result = COUNTROWS(EXCEPT(__Table2, __Table1))
RETURN
__Result
Dear @Greg_Deckler
it says for every table argument of EXCEPT it must be the same column number which is not 😞
@gianmarco It should be if you use the same "__Company" column name. That's the reason to use SELECTCOLUMNS. I mocked it up and it seems to work. See attached PBIX below signature.
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 |
---|---|
103 | |
101 | |
87 | |
73 | |
67 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |