Key word filter (maybe?)

[dniedrauer]

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 impact of vessel transfers (into a given area and out). But as you can see, I have lookup tables "old" and "new" and obviously you can only apply one at one time.  E.g. see below. 

I want to be able to create a filter so that when I select an Area from a dropdown, I can create measures for both sales where old = dropdown and new = not dropdown value AND another measure for sales where old =not dropdown and new = dropdown. I need to be display both of these values simultaneously. 

Not sure how to describe this, but it's really easy to do in Excel with a Macro that plays on Slicers. How would I go about this in Power BI? 

[dniedrauer · Accepted solution]

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

[v-jiascu-msft]

Hi @dniedrauer,

If you want another solution (MAYBE there is), please share a dummy .pbix file.

Best Regards!

Dale