Forum Discussion
Resolver ICreate a measure that returns values like a Field Parameter does
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
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]) )
4 Replies
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.
user01Anonymous
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.
- user01
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]) )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.
