Forum Discussion
Measure based on Parameter
I'm trying to use the parameter function in PBI desktop to find cost index: countries' unit costs divided by a baseline country's unit costs. The baseline country is the parameter country that I would change. Here is the current measure DAX that isn't working:
Cost Index = DIVIDE([Unit Cost],CALCULATE([Unit Cost],CountryParameter[CountryParameter]))
The measure of [Unit Cost] is working, so the problem isn't there.
Here's how my parameter is currently set up:
I've tried it both related to the country dimension table and with no relationships at all. I believe it isn't supposed to be related to anything.
Thoughts?
I figured it out, so I'll post what I did on here in case someone comes across this in the future.
I created a separate blank query then "entered data" by pasting a column of all unique country names. Then measure DAX:
Client Cost Index = CALCULATE(DIVIDE([Unit Cost],CALCULATE([Unit Cost],Vendors[VendorCountry]=VALUES(Parameter[ParameterCountry]))),FACTS[FactSource]="Client")
Then just throw another slicer on the dashboard and drop the [parametercountry] in it. It'll show an error on any visual unless only one selection on that slicer is selected.
3 Replies
I've tried adding in that the country dimension is equal to the parameter as well
Cost Index = DIVIDE([Unit Cost],CALCULATE([Unit Cost],Vendors[Country]=CountryParameter[CountryParameter]))
no luck
Cost Index = DIVIDE([Unit Cost],CALCULATE([Unit Cost],Vendors[Country]=VALUES(CountryParameter[CountryParameter])))
I changed it to this ^ and got numbers that look right when the parameter is set to China. but now when i change the parameter to say, Vietnam, it isn't updating anything.
Thoughts?
I figured it out, so I'll post what I did on here in case someone comes across this in the future.
I created a separate blank query then "entered data" by pasting a column of all unique country names. Then measure DAX:
Client Cost Index = CALCULATE(DIVIDE([Unit Cost],CALCULATE([Unit Cost],Vendors[VendorCountry]=VALUES(Parameter[ParameterCountry]))),FACTS[FactSource]="Client")
Then just throw another slicer on the dashboard and drop the [parametercountry] in it. It'll show an error on any visual unless only one selection on that slicer is selected.