Create a measure that returns values like a Field Parameter does

[user01]

I have some dummy data below. I imported the example from Excel, but the true data will come from SQL Server via DirectQuery.

I have a slicer for Category and a slicer for the Field Parameter. The Field Parameter uses Amount column and Employee column. The Field Parameter is called "View" in my example.

I put the actual columns and the Field Parameter in a Table visualization. I want to create a measure that returns values like the Field Parameter does ("Desired" column in attached image). So, if the user chooses Amount, the desired measure returns the value for Amount, and similarly for Employee.

I'm trying to figure out a workaround for conditional formatting. I cannot link to my main issue right now because it was marked as spam.

Desired Example:

Dummy data:

Category	State	Employee	Amount
A	Virginia	68	1114208
A	Utah	144	1075717
A	Michigan	38	1074534
A	Illinois	115	1074214
A	Nevada	104	1067658
A	Massachusetts	93	1065064
A	New Hampshire	42	1005700
A	California	105	946129
A	Maine	101	905930
A	Washington	134	878621
A	New Jersey	76	851987
A	Pennsylvania	110	850507
A	West Virginia	77	828861
A	Rhode Island	88	811964
A	Connecticut	70	802208
A	New York	129	798794
A	Delaware	114	783194
A	Idaho	18	734638
A	Maryland	125	706365
A	Tennessee	69	688048
A	Wisconsin	97	668750
A	Kansas	65	665809
A	Mississippi	132	580631
A	Vermont	106	575309
A	Georgia	177	520854
B	Wisconsin	97	1706845
B	Massachusetts	93	1477807
B	Maine	101	1394473
B	Idaho	18	1358479
B	Rhode Island	88	1184244
B	New Jersey	76	1177116
B	Utah	144	1143117
B	Tennessee	69	1084389
B	Pennsylvania	110	1032075
B	West Virginia	77	892761
B	Connecticut	70	865538
B	Kansas	65	864826
B	Virginia	68	862125
B	California	105	848612
B	New York	129	818033
B	Washington	134	764280
B	Maryland	125	710922
B	Georgia	177	708657
B	Vermont	106	698224
B	Illinois	115	695709
B	Mississippi	132	675824
B	Delaware	114	657883
B	Michigan	38	630631
B	Nevada	104	539081
B	New Hampshire	42	484559

[Anonymous]

Hi @user01 ,

I create a table as you mentioned.

Then I think you can create a measure and here is the DAX code.

SelectedValueMeasure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Parameter'[Parameter]) = "Amount", SUM('Table'[Amount]),
    SELECTEDVALUE('Parameter'[Parameter]) = "Employee", SUM('Table'[Employee])
)

You can use them and it will give you what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[user01 · Accepted solution]

@Anonymous 
I made a change to your code and it worked. But do you know why it did not work when I did it your way?

SelectedValueMeasure =
SWITCH(
    TRUE(),
    SELECTEDVALUE('Parameter'[Parameter Fields]) = "'Table'[Amount]", SUM('Table'[Amount]),
    SELECTEDVALUE('Parameter'[Parameter Fields]) = "'Table'[Employee]", SUM('Table'[Employee])
)

[Anonymous]

Hi @user01 ,

From the error message, the problem is because the Parameter column is part of the composite key and your expression does not contain all the columns of the composite key.

So it needs to be presented with the full column, like the 'Table'[Amount] you modified.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[user01]

@Anonymous 

I got the following error:

Error fetching data for this visual

MdxScript(Model) (12, 5) Calculation error in measure 'Data2'[SelectedValueMeasure]: Column

[Parameter] is part of composite key, but not all columns of the composite key are included in the

expression or its dependent expression.