Forum Discussion
Key word filter (maybe?)
- 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....
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-msft8 years agoMicrosoft Employee
Hi dniedrauer,
If you want another solution (MAYBE there is), please share a dummy .pbix file.
Best Regards!
Dale