Forum Discussion
SWITCH Function not giving correct result
Hey 🙂 I have a problem with a task and maybe someone here can help me to solve it. I have listed projects and their revenue in the Excel table. Now I want to visualize the data and create a chart using Power BI by dividing projects into category S and C. Category C includes all projects with revenue more than 50 and S includes all projects with revenue less than 50. When I use the DAX formula:
ProjectCategory =
VAR Revenue = SELECTDATAVALUE( XXX [YYY])
RETURN
SWITCH (TRUE(),
Revenue >= 50, "C",
Revenue <= 50, "S")
only projects belonging to category S are represented in the diagram and projects belonging to category C are not included. Does anyone have an idea how I can fix this?
Thank you very much and have a nice evening! 🙂
Hi,
If you are creating a calculated column (not measure) in the table, please try the below to create a new column.
ProjectCategory = VAR Revenue = XXX[YYY] RETURN SWITCH ( TRUE (), Revenue >= 50, "C", Revenue <= 50, "S" )Anonymous
Thank you for the clarification. Unfortunately it doesn't work like this. You need to create a calculated column as suggested by Jihwan_Kim and then use it to slice you visual. Then you can create a measure such as COUNTROWS ( TableName ) or just drag the Project column in the Values of the visual ans summarize it by count or distinctcount as needed.
Hi Anonymous ,
Please try below steps:
1.Add a new column to table
Project Catagory = SWITCH ( TRUE, 'Table'[Planned Revenue] <= 50, "S", 'Table'[Planned Revenue] > 50, "C" )2.Visualize the data with pie chart
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
Hey Jihwan_Kim, tamerj1 and Anonymous it works! Thank you very much guys! 😄
Hi,
If you are creating a calculated column (not measure) in the table, please try the below to create a new column.
ProjectCategory = VAR Revenue = XXX[YYY] RETURN SWITCH ( TRUE (), Revenue >= 50, "C", Revenue <= 50, "S" )Hi,
Please share your pbix file's link here, and then I can try to look into it to come up with a more accurate solution for your data model.
Thanks.
Hi Anonymous
double check the data type. The column XXX [YYY] should be of type integer or decimal.
Hi Anonymous ,
Please try below steps:
1.Add a new column to table
Project Catagory = SWITCH ( TRUE, 'Table'[Planned Revenue] <= 50, "S", 'Table'[Planned Revenue] > 50, "C" )2.Visualize the data with pie chart
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Jihwan_Kim and tamerj1, thank you very much guys for your help. I created a new similar Project without important Data and made some Screenshots to show you 🙂
Anonymous
Thank you for the clarification. Unfortunately it doesn't work like this. You need to create a calculated column as suggested by Jihwan_Kim and then use it to slice you visual. Then you can create a measure such as COUNTROWS ( TableName ) or just drag the Project column in the Values of the visual ans summarize it by count or distinctcount as needed.
Dear tamerj1, I created a calculated column as you and Jihwan_Kim suggested but it still doesnt work. As you can see in every row of this created column is S, even when projects x, c and df > then 50 and belong to C. Do you have any Idea why?