Forum Discussion
Create Calculated Fields based on Field Parameter selected
Hi Everyone
I would like to create a calculated field that is based on what I select from Field Parameters.
I created a field parameter that contains Column A (all numbers) and Column B (all numbers).
Then I tried creating a new calculated column that should be divided by the values from Column A or B, depending on the user selection. I tried it by using DAX and Power Query, but I can't seem to get it to work.
How do i go about doing this? Any help will be appreciated
Thanks
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a field parameter
3. create a measure with below dax formula
Measure = VAR tmp = SELECTCOLUMNS ( Parameter, "SelectColumn", [Parameter] ) VAR _str = CONCATENATEX ( tmp, [SelectColumn] ) VAR _val = SWITCH ( _str, "Column1", SUMX ( SELECTCOLUMNS ( 'Table', "Column1", [Column1] ), [Column1] ), "Column2", SUMX ( SELECTCOLUMNS ( 'Table', "Column2", [Column2] ), [Column2] ) ) VAR _val1 = 100 RETURN DIVIDE ( _val, _val1 )4. add a table visual and a card visual
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a field parameter
3. create a measure with below dax formula
Measure = VAR tmp = SELECTCOLUMNS ( Parameter, "SelectColumn", [Parameter] ) VAR _str = CONCATENATEX ( tmp, [SelectColumn] ) VAR _val = SWITCH ( _str, "Column1", SUMX ( SELECTCOLUMNS ( 'Table', "Column1", [Column1] ), [Column1] ), "Column2", SUMX ( SELECTCOLUMNS ( 'Table', "Column2", [Column2] ), [Column2] ) ) VAR _val1 = 100 RETURN DIVIDE ( _val, _val1 )4. add a table visual and a card visual
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- I have a similar situation where I have four columns that are all text columns. I have a field parameter slicer where I will need to select multiple parameters. If possible, In addition to returning the values in those 4 columns in a table visual, I am hoping to create an additional column that will concatenate the values for each row with the pipe delimiter ("|". Using the example above, column 3 would be returned 10|15 if both column 1 and 2 were selected in the parameter selection.
Hope the latter part of this video helps(from the 8 minute mark onwards) : https://www.youtube.com/watch?v=7mlYt6x-XCo&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=31
What you're looking for is probably using a measure instead of calculated columns. Calculated columns get created at refresh time. I've never tried creating calc columns based on field parameters but no wonder if you can't
Hope this helps:
