Forum Discussion

dniedrauer's avatar
dniedrauer
Resolver I
8 years ago
Solved

Key word filter (maybe?)

As shown above, I have this table called transfers. It contains the data show below. In the table called "Main" there is transaction data.    I would like to be able to quantify the net...
  • dniedrauer's avatar
    8 years ago

    I did solve the problem myself, but it was a bit complicated and maybe not the best solution. 

     

    I created a calculated column called "match" that classified each vessel transfer as "Inter Area", "Intra Area", "Lost", and "New" depending on how the "old area" and "new area" columns lined up by row, e.g. (blank under "new area" with a value under "old area" = "lost"). 

     

    I first created two measure firstnoblanknew and firstnoblankold to correspond to the "Old" customer info and the "New" customer info filters. I then created two measures: one for measuring sales to vessels transferred out of the selected area: Out = calculate([Spend USD],filter(ALL('Cust info New'[New Area]),FIRSTNONBLANK('Cust info Old'[Old Area],1)=[FirstNoBlank]),or(Transfers[Match]="Inter Area",Transfers[Match]="Lost"))*-1.

     

    and one for vessels transferred into the selected area: In = calculate([Spend USD],filter(ALL('Cust info Old'[Old Area]),FIRSTNONBLANK('Cust info New'[New Area],1)=[firstnoblanknew]),or(Transfers[Match]="Inter Area",Transfers[Match]="New"))

     

    It's only a little clunky because now, to get the net effect of vessels transferred in and out to a selected area, you have to select both the new Area and the old Area to the Area you want to analyze on a slicer to get the firstnonblank values to work properly. 

     

    The result seems to work. I wish there were an easier way....