Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all I have a table
With Columns:
ID | platform | status |
1 | a | active |
2 | a | active |
3 | a | Inactive |
4 | b | active |
5 | b | active |
6 | b | Inactive |
7 | c | active |
8 | c | active |
9 | c | active |
Another table with columns:
ID | Product | platform |
1 | x | a |
2 | x | a |
4 | y | b |
5 | y | b |
7 | y | c |
8 | y | c |
9 | y | c |
Now the product x is only for ID 1 & 2 so other ID do not have product 1 & 2 so If I have a bar plot with
x - axis product
y- axis count
and if I drill thorough I need to get the delta so, for product x it is 4, 5, 7 8, 9 has they are active and do not have have product x
I would use SQL NOT EXISTS and get the delta but Here I would like to have filter and drill through functionality so, not understanding what to use particularly.
Thank you for your time and help.
Solved! Go to Solution.
If I understand this correctly, I think you want my Inverse Aggregator Quick Measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266
I have created a measure that is giving me the count
CALCULATE(COUNT(TABLE1[ID]), TABLE1[Status] = 'Active') - DISTINCTCOUNT(TABLE2[ID])
BUT Instead of Numbers I want to display the actual ID @Greg_Deckler
Here I am trying to display all the IDs that are not present instead of sum!
so ouput we be something like
ID |
4 |
5 |
7 |
8 |
9 |
It's really pretty much the same thing:
Measure 14 = VAR __ids = SELECTCOLUMNS('Table19',"__id",[ID]) VAR __allIds = ALL('Table18'[ID]) VAR __otherIds = EXCEPT(__allIds,__ids) RETURN CONCATENATEX(__otherIds,[ID],", ")
See Page 6, Tables 18 and 19.
@Greg_Deckler Thanks a lot for this,
But What I really want is output as table, As I would Have 1000's of Id and I would like to link those ID's to other table and extract some other information.
I tried Figuring it out! But Can't get it to work.
Hope no one minds, but decided to take crack at this one. Since the end goal is a table which then can be related to other tables, I used Power Query.
So you get this data model, which can use the new left anti join table to relate to other tables
Here's what the matrix looks like:
Then to update ( little more cumbersom than just a slicer.. but)
Just another way of looking at it I guess, not sure it's what you had in mind though. Below you will find the PBIX file if you are so inclined:
@Anonymous Thank you for showing me an other solution.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |