Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Finding Delta

Hello all I have a table 

 

With Columns: 

 

IDplatformstatus
1aactive
2aactive
3aInactive
4bactive
5bactive
6bInactive
7cactive
8cactive
9cactive

 

 

Another table with columns:  

IDProductplatform
1xa
2xa
4yb
5yb
7yc
8yc
9y

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.
 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Not applicable

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
Not applicable

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

Data Model.png

Here's what the matrix looks like:

Matrix.png

Then to update ( little more cumbersom than just a slicer.. but)

Update Parameter.png

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
Not applicable

@Anonymous  Thank you for showing me an other solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.