Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SriGaG1
Helper II
Helper II

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. 

 

SriGaG1_0-1670608359662.png

 

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.. 

1 ACCEPTED 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:

vjianbolimsft_0-1670917245831.png

vjianbolimsft_1-1670917259368.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

Hi @SriGaG1 ,

 

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

vjianbolimsft_0-1670808923727.png

Then select the two columns:

vjianbolimsft_1-1670808967217.png

Rename these fields:

vjianbolimsft_2-1670809014450.png

Then use the parameter to create a visual:

vjianbolimsft_3-1670809061534.png

Final output:

vjianbolimsft_4-1670809090054.png

vjianbolimsft_5-1670809101568.png

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:

vjianbolimsft_0-1670917245831.png

vjianbolimsft_1-1670917259368.png

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.

Mikelytics
Resident Rockstar
Resident Rockstar

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.