Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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..
Solved! Go to Solution.
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.
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.
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
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.
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.
-----------------------------------------------------
@Mikelytics Thank you very much for your answer. Its not workin actually. I have tried that one too 😞
@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