Conditional column Based on Switch function - Error

Hello Experts,

I have an issue in writing a DAX function for the below scenario

I have a custom table wich contains only one single column named "Satisfaction type" and it contains two rows named "Overall" and "Safety" .

In the fact table, I do have customer data sheet and it contains two columns named "Customer overall Satisfaction Rating" and "Customer Safety Satisfaction Rating" each column contain values starting from 1 to 5.

What I need is to create a switch function. When I select safety from the above custom table, it should show the "Customer Safety Satisfaction Rating" . in the other option, it should show "Customer overall Satisfaction Rating" column

this is the code I wrote

Total Satisfaction = SWITCH(Satisfction[Satisfaction Type],
"Overall",MAX(Data[Customer_overall_Satisfaction_Rating]),
"Safety",MAX(Data[Customer_Safety_Satisfaction_Rating]))

But as a result, I am getting only the value 5 for both of the selections.

But for my visualization I need all of the data. That means when selected "Overall" I need to get 1,2,3,4 and5. not only just 5. I know that number 5 is coming because we are using MAX function in the dax function. Is there any turn around we can do for this?

Pls help..

v-jianboli-msft
3 years ago
Hi SriGaG1 ,

You can add a measure to the visual:

Measure = VAR _a = SWITCH ( MAX ( Parameter[Parameter] ), "Overall", MAX ( 'Table'[Customer overall Satisfaction Rating] ), "Safety", MAX ( 'Table'[Customer Safety Satisfaction Rating] ) ) VAR _b = SWITCH ( _a, 1, "dissatisfied", 2, "dissatisfied", 3, "neutral", 4, "satisfied", 5, "satisfied" ) RETURN _b

Final output:

Besides, this question is beyond the original topic of this thread, so if you still have any other questions, please consider marking the responses that are helpful to you and creating a new thread in order to better help others with similar questions. This will make the post more relevant and will allow more people to help you!

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Conditional-column-Based-on-Switch-function-Error.pbix28 KB

6 Replies

Mikelytics
Resident Rockstar
3 years ago
Hi SriGaG1

I am pretty sure that you need to add the SELECTEDVALUE function:
Total Satisfaction = SWITCH( SELECTEDVALUE(Satisfction[Satisfaction Type]), "Overall",MAX(Data[Customer_overall_Satisfaction_Rating]), "Safety",MAX(Data[Customer_Safety_Satisfaction_Rating]) )
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
LinkedIn
- SriGaG1
  Helper II
  3 years ago
  Mikelytics Thank you very much for your answer. Its not workin actually. I have tried that one too 😞
  - Mikelytics
    Resident Rockstar
    3 years ago
    SriGaG1 @Can you please show a picture of the data model? Do you added a relationship? If yes, you have to remove the relation.
    
    Best regards
    Michael
v-jianboli-msft
Community Support
3 years ago
Hi SriGaG1 ,

Based on your description, it seems that the fields parameter could help you.

Then select the two columns:

Rename these fields:

Then use the parameter to create a visual:

Final output:

For more details, please refer to:

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Learn

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Conditional-column-Based-on-Switch-function-Error.pbix27 KB
- SriGaG1
  Helper II
  3 years ago
  Thank you very much. Seems like its working. But one question before closing this off. How to get the particular column that we wrote in to a dax query? What I meant was, I do need to group them like
  if parameter = 1 then "dissatisfied"
  elseif parameter = 2 then "dissatisfied"
  elseif parameter = 3 then "neutral"
  elseif parameter = 4 then "satisfied"
  elseif parameter = 5 then "satisfied"
  
  Its a blocker for me. And from your method, we cannot put it to the filters. Andyou give me some points onthe above things.
  
  Thankyouverymuc for your time and effort. v-jianboli-msft
  - v-jianboli-msft
    Community Support
    3 years ago
    Hi SriGaG1 ,
    
    You can add a measure to the visual:
    
    Measure = VAR _a = SWITCH ( MAX ( Parameter[Parameter] ), "Overall", MAX ( 'Table'[Customer overall Satisfaction Rating] ), "Safety", MAX ( 'Table'[Customer Safety Satisfaction Rating] ) ) VAR _b = SWITCH ( _a, 1, "dissatisfied", 2, "dissatisfied", 3, "neutral", 4, "satisfied", 5, "satisfied" ) RETURN _b
    
    Final output:
    
    Besides, this question is beyond the original topic of this thread, so if you still have any other questions, please consider marking the responses that are helpful to you and creating a new thread in order to better help others with similar questions. This will make the post more relevant and will allow more people to help you!
    
    Best Regards,
    
    Jianbo Li
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Conditional-column-Based-on-Switch-function-Error.pbix28 KB