Dynamic referencing in measure

[phillip c]

Hi All,

I have the following 2 measures:
Customer Sales:=CALCULATE(SUMx('hourly',hourly[Total sales]),FILTER('hourly',hourly[Customer Code]=275))
Supplier Sales:=CALCULATE(SUMx('hourly',hourly[Total sales]),FILTER('hourly',hourly[Supplier Code]=275))
which work just fine.
What I'm trying to achieve is have the customer code be selected dynamically based on a slicer. The reason I'm not just using a slicer based on customer/supplier is because I would like to see data for a certain company as both a customer AND a supplier in the same table. A slicer for a customer would filter our instances where that company is a supplier and vice versa.
Any help would be much appreciated.

Thanks,

Phillip

 

[scottsen]

I think you will need to have the codes in a separate table that is not related. Let's call it CustomerCodes.

Define a new measure:
[SelectedCustomerCode] := MIN(CustomerCodes

)

(if multiple are selected, its just going to return the first one...)

Then where you have 275 in your measures above, just use [SelectedCustomerCode] instead.

 

[phillip c]

Hi Scottsen,

Thanks a lot for your reply. I ended up using a solution similar to the one you proposed, but I actually had to add not 1, but 2 unrelated tables.
If anyone is interested in the solution (and the problem), I can post it.