Finding Delta

[Anonymous]

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.

[Anonymous · Accepted solution]

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 1

Thank you @Greg_Deckler  and @Anonymous  for your time.

[Greg_Deckler]

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

[Anonymous]

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 

[Anonymous]

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

[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. 

[Anonymous]

@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.

[Anonymous]

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.

• Loaded your two tables from above (Named one Status and Platform)
• Duplicated the Platform table and names Platform Selected. Renamed the original platform Table as Platform Not Selected
• Created a unique list of Products
• Created a parameter using that unique list to choose from 
  • I used that selection to filter the "Platform Selected" table
  • Then did a left anti join between Platform Not Selected and Platform Selecte
  • this results in all the products that were not selected
  • Loaded all three tables ( though probably only need the platform selected table)

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:

https://1drv.ms/f/s!Amqd8ArUSwDSzzlF4Fn-ipKTw25i

[Anonymous]

@Anonymous  Thank you for showing me an other solution.