Getting the "missing" or new items in a package.

[Anonymous]

I have a provided a very simple example of the "datamodel" and some very basic data. Each Package contains one or more items.  Each item can exist in mutliple packages for this I have created a bridge table to handle the many to many relationship.

What I need to do in a Report:

I need to be able to dynamically determine which Items exist in one Package and not another. 

From the List of Packages the user will pick a Source Package and then the Target Package.

If Package "A" is the “source” and Package "C" is the “Target”.. Be able to get a listing of all the Items in Package C that are NOT in Package A.  So in Package C only show Items 4 and 5 as they don't exist in Package A.   

If the user selected package B as the source and Package C as the target then items 2,3,4 would be presented.

Looking for how to implement this in a Report.. assuming this shoudl be done in DAX... report users need to see the delta between any source and target selections..

Thanks for any help in advance

[Greg_Deckler]

@Anonymous - Perhaps create a measure like this:

Measure =
  VAR __Source = SELECTEDVALUE('Packages'[Package])
  VAR __Target = SELECTEDVALUE('Packages2'[Package])
  VAR __Table = 
    EXCEPT(
      SELECTCOLUMNS(FILTER('Package_Items',[Package]=__Target),"Package",[Package])
      SELECTCOLUMNS(FILTER('Package_Items',[Package]=__Source),"Package",[Package])
    )
RETURN
  IF(MAX('Package_Items') IN __Table,1,0)

It's basically a Complex Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[Anonymous]

@Greg_Deckler , 

Thanks first for your quick reply

So Not sure this is working..    or I am not using it correctly.. 

1) assume there was a Typo in the measure in that shoudl be a "," between the two selectcolumn funtions?

2) assume that I had to create a second / copy of the Packages table to create the Packages2 refreencd in the __Target var?

So on my report I'd like to Have TWO Slicers for the user to pick the "Source" and "Target" individually.

Then in a table show the Name of the Target Package and each Item that is in the Target Package but NOT in the Source Package.  

If I deselect the Source package then the table would show ALL the items for the Target Package.

How does this measure determine what Items to show?

I looked at the links you included for the complex selectors as well, thanks for the references.. 

[Greg_Deckler · Accepted solution]

@Anonymous Sorry for the syntax issues. I corrected those and have included a PBIX file below with a couple variations. Hopefully this will be enough to get the general technique down.

Measure = 
  VAR __Source = SELECTEDVALUE('SourcePackages'[Packages])
  VAR __Target = SELECTEDVALUE('TargetPackages'[Packages])
  VAR __Table = 
    EXCEPT(
      SELECTCOLUMNS(FILTER(ALL('PackageItems'),[Packages]=__Target),"Item",[Items]),
      SELECTCOLUMNS(FILTER(ALL('PackageItems'),[Packages]=__Source),"Item",[Items])
    )
RETURN
   IF(MAX('PackageItems'[Items]) IN __Table,1,0)

Measure 2 = 
  VAR __Source = SELECTEDVALUE('SourcePackages'[Packages])
  VAR __Target = SELECTEDVALUE('TargetPackages'[Packages])
  VAR __Table = 
    EXCEPT(
      SELECTCOLUMNS(FILTER(ALL('PackageItems'),[Packages]=__Target),"Item",[Items]),
      SELECTCOLUMNS(FILTER(ALL('PackageItems'),[Packages]=__Source),"Item",[Items])
    )
RETURN
   CONCATENATEX(__Table,[Item],",")

[Anonymous]

@Greg_Deckler ,

Your Measures WORKED GREAT.. Thanks so much.

More importantly pointing me to the Posts on complex selectors really helped.  I was able to make the Inverse Selector example work as well.  

Thanks again for the help..