Make a calculated column return different dimensions based on parameter selection

[ExcelUser]

Hi,

I would like the user to be able to display either an abbreviated or full Review Type, based on a selection in a slicer they choose. The slicer is based on a parameter I made.

I tried making a calculated column but it's not working as intended. I was expecting [Review] to look for the string in the parameter options and then return a desired dimension. But it's only returning Blank as far as I can tell.

Thank you for your help!

I've uploaded sample PBIX and XLSX to dropbox: 

Data: https://www.dropbox.com/scl/fi/vba3v37tm8ev4fo9lbs72/test_data.xlsx?rlkey=xl30t5mzwiczzftaqj6ui875v&...

Dashboard: https://www.dropbox.com/scl/fi/dw0ykhhz81plxvyaooj4g/dummy-dashboard.pbix?rlkey=rigpbdczptqh0uqkzcbi...

Review =

SWITCH(

    TRUE(),

    SELECTEDVALUE('PSI/PC-06 Review Details'[PSI/PC-06 Details]) = "Aggregate Review Types",test_data[REVIEW TYPE],

    SELECTEDVALUE('PSI/PC-06 Review Details'[PSI/PC-06 Details]) = "Separate Review Types",test_data[Full Review Type],

    BLANK()

)

[gmsamborn · Accepted solution]

Hi @ExcelUser 

Calculated columns aren't affected by slicers since they are calculated during refresh.

I replaced your calculated column with the following measure:  (After re-reading your post, your code should work as a measure - NOT a calculated column.  The calculated column in your pbix is missing the TRUE() line changing the meaning of the whole SWITCH() statement.)

Review = 
    SWITCH(
        SELECTEDVALUE('PSI/PC-06 Review Details'[PSI/PC-06 Details]),
            "Aggregate Review Types", 
            MIN( test_data[REVIEW TYPE] ),
            "Separate Review Types",  
            MIN( test_data[Full Review Type] )
    )

Let me know if you have any questions.

MY dummy dashboard.pbix

[ExcelUser]

Thank you for taking the time to answer. Your solution helped solved my problem.

Thanks for the FYI about calculated columns being refresh-dependent.

[gmsamborn]

@ExcelUser 

I'm glad it helped.

[ExcelUser]

Hi @gmsamborn ,

When implementing your solution into my actual data, I realized I made an error in my dummy data. I included a distinct ID number in the table, which I'm guessing is why the Min(Review Type) expression you suggested worked. 

My actual dashboard won't have distinct account numbers. It is a table where all the meausures of the review types are summed. I'm getting 1 row of data, the MIN of the Review Type.

I re-uploaded your dashboard, and added some dummy measures to mimic the look of the actual dashboard. I also put in some screenshots of what I was hoping to replicate.

dashboard(edited): https://www.dropbox.com/scl/fi/9ychwadt15ls95rr0eqrr/dummy-dashboard.pbix?rlkey=44rq170y4i26cc5lgt3t...

test data (edited): https://www.dropbox.com/scl/fi/0hjmcjggliawa1mah1ez8/test_data.xlsx?rlkey=ebem6ogd9qes37w387e6rgmdx&...

[gmsamborn]

Hi @ExcelUser 

I set up a Field Parameter to replace your 'PSI/PC-06 Review Details' table and renamed it.  By using the [Axis] column in the visual makes things a lot easier.

Take a look at Page 2 of my pbix.

dummy dashboard - try 2.pbix

https://www.sqlbi.com/articles/fields-parameters-in-power-bi/

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[ExcelUser]

Gotcha. Thanks so much for taking another look. This works perfectly. 

[gmsamborn]

I'm glad it worked for you.