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'd like to write a measure to calculate total distinct items from 1 column that not found in another column. 2 columns are from different tables.
I was able to do this my adding a calculated helper column, but just wondering if there is any way to do it without adding an extra column.
For example:
Column A from table 1
-----------
AA
AA
AA
BB
CC
CC
DD
DD
EE
Column B from table 2
-----------
AA
AA
BB
BB
FF
I want to count distinct items from Column A not found in Column B.
There are 5 items in Column A not found in Column B:
-----------
AA
AA
AA
BB
CC
CC
DD
DD
EE
And there are 3 distinct counts: CC DD and EE, so the measure should return 3.
Thanks!
Solved! Go to Solution.
@yuanye0710 , except can help
In A not in B
a new table = except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) )
measure
a new table = Countrows(except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) ) )
@yuanye0710 , except can help
In A not in B
a new table = except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) )
measure
a new table = Countrows(except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) ) )
Thanks a lot! it's working for me!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |