Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 a...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    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.
     
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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