Forum Discussion
Finding Delta
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.
- Measure =VAR __ids = SELECTCOLUMNS('Table19',"__id",[ID])VAR __allIds = ALL('Table18'[ID])VAR __otherIds = EXCEPT(__allIds,__ids)RETURN IF(SELECTEDVALUE(Table18[ID], 0) IN (__otherIds), 1, 0)The above measure is working as I wanted it to. by setting a visual filter for a table visual. where Measure IS 1Thank you Greg_Deckler and Anonymous for your time.
8 Replies
- Greg_Deckler
Community Champion
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
- Greg_Deckler
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.
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
- Measure =VAR __ids = SELECTCOLUMNS('Table19',"__id",[ID])VAR __allIds = ALL('Table18'[ID])VAR __otherIds = EXCEPT(__allIds,__ids)RETURN IF(SELECTEDVALUE(Table18[ID], 0) IN (__otherIds), 1, 0)The above measure is working as I wanted it to. by setting a visual filter for a table visual. where Measure IS 1Thank you Greg_Deckler and Anonymous for your time.
