Forum Discussion
how to parameterize a calculated column
Dear,
How can I have the user select through some sort of parameter what kind of data he wants to see?
For example, I want to give the user the ability to switch ParameterX between the values "A" and "B".
My calculated column would be something like:
MyCalculatedColumn = SWITCH(Parameter,"A", "A is chosen", "B", "B is chosen", "")
I don't want to create two separate versions of the calculated column (like "MyCalculatedOptionForValueA" and "MyCalculatedOptionForValueB") because the column is referred to in a lot of other calculated columns and would therefore end in a duplication of all related columns...
Thanks
Small update: We have been redesigning the data flow, so we generate all the different versions of the calculated field; with this we can use the "version indicator" as a slicer.
to illustrate a bit, this was the situation that we tried:
TableA with [ID], [ColumnA] and [MyCalculatedColumn] where
[MyCalculatedColumn] = IF(ParameterA = true, [ColumnA] & " ParameterA is true", [ColumnA] " ParameterA is false")
the new situation:
Table A With [ID], [ColumnA]
Table B With columns [TableA_ID], [ParameterA] and [MyCalculatedColumn]
6 Replies
Hi KJanssens ,
By your description, It looks like you are looking for What-if parameters in Power BI.
https://www.absentdata.com/power-bi/parameter-power-bi/
Check the above link it shows you an example on how you can use this capability.
Thanks,
Pragati
Hi Pragati11 ,
that looked promising, but I can't get it to work.
I have made a test file (https://www.dropbox.com/s/sax8hwpejux9jrw/ParameterTest.pbix?dl=0) ...
I have added a calculated field that depends on the parameter:
MyCalculatedColumn = SWITCH(Parameter[Parameter Value], 0, "ZERO CHOSEN" & 'Table'[Column1], 1, "ONE CHOSEN" & 'Table'[Column1], "OOPS" & 'Table'[Column1])but it always show "OOPS[Column1]" ...
KJanssens , Not very clear. you can not parameterize column, you can do with measure.
In case you are looking for measure slicer
measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://www.youtube.com/watch?v=vlnx7QUVYMEDynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0MHi amitchandak ,
thanks for the examples. However, those examples are showing how to parameterize a measure using another measure, but I want to parameterize a column with a measure...
HI KJanssens,
AFAIK, you can't create a dynamic calculated column/table based on filter/slicer selections. They are not worked on the same data level and calculated fields will be fixed before slicer/filters.
I'd like to suggest you create a query parameter and save it as a query table, then you cna use the calculated column formula to interact with current query parameter values. (query table are the parent level of data model tables)
Regards,Xiaoxin Sheng
