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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors