SWITCH Function not giving correct result

[Anonymous]

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! 🙂

[Jihwan_Kim · Accepted solution]

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" )

[tamerj1 · Accepted solution]

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

[Anonymous]

Hey @Jihwan_Kim, @tamerj1 and @Anonymous it works! Thank you very much guys! 😄

[Anonymous · Accepted solution]

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.

[Anonymous]

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 🙂

 

 

 

[tamerj1 · Accepted solution]

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

[Anonymous]

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? 

 

[tamerj1]

Hi @Anonymous 
Delete SELECTEDVALUE. Just use Table[Column] directly

[tamerj1]

Hi @Anonymous 

double check the data type. The column XXX [YYY] should be of type integer or decimal. 

[Jihwan_Kim · Accepted solution]

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]

Hey 🙂 thank you very much for your suggestion! But it still doesn't works 😞

[Jihwan_Kim]

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.

[Anonymous]

Thank you very much! But I'm not allowed to share the link because of the confidential data... 😞