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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rosscortb
Post Patron
Post Patron

value format error in if statement

Hello,

 

What do I need to do to fix this format/value error please?

Column = IF('Spans & Layers'[Direct Reports] > 10,"Red",IF('Spans & Layers'[Direct Reports] >=1 <= 2,"Red",IF('Spans & Layers'[Direct Reports] >= 6 <=10,"Green",IF('Spans & Layers'[Grade] = "S1"||"S2"||"S3"||"S4","Black","Orange"))))
 
 
Thanks
Ross
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @rosscortb ,

You can modify your dax like this:

Column = 
IF('Spans & Layers'[Direct Reports] > 10,"Red",
IF('Spans & Layers'[Direct Reports] >=1 || 'Spans & Layers'[Direct Reports] <= 2,"Red",
IF('Spans & Layers'[Direct Reports] >= 6 || 'Spans & Layers'[Direct Reports]<= 10,"Green",
IF('Spans & Layers'[Grade] in {"S1","S2","S3","S4"},"Black","Orange"))))

 

Best Regards,

Liu Yang

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-yangliu-msft
Community Support
Community Support

Hi  @rosscortb ,

You can modify your dax like this:

Column = 
IF('Spans & Layers'[Direct Reports] > 10,"Red",
IF('Spans & Layers'[Direct Reports] >=1 || 'Spans & Layers'[Direct Reports] <= 2,"Red",
IF('Spans & Layers'[Direct Reports] >= 6 || 'Spans & Layers'[Direct Reports]<= 10,"Green",
IF('Spans & Layers'[Grade] in {"S1","S2","S3","S4"},"Black","Orange"))))

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rosscortb
Post Patron
Post Patron

@Tahreem24 to be used in a conditional formating, and I need this formula due to the two columns

Tahreem24
Super User
Super User

@rosscortb I tried formulate it as per my understanding. Try this out and let me know if it's giving you correct result or not. 


Column = IF('Spans & Layers'[Direct Reports] > 10,"Red",IF('Spans & Layers'[Direct Reports] >=1 && 'Spans & Layers'[Direct Reports] <= 2,"Red",IF('Spans & Layers'[Direct Reports] >= 6 && 'Spans & Layers'[Direct Reports] <=10,"Green",IF('Spans & Layers'[Grade] = in {"S1","S2","S3","S4"},"Black","Orange"))))

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 it had errors with <=  and then in.

So, changed to this but still has the value and format error as seen in my previous screenshot

Column = IF('Spans & Layers'[Direct Reports] > 10,"Red",IF('Spans & Layers'[Direct Reports] >=1 <= 2,"Red",IF('Spans & Layers'[Direct Reports] >= 6 <= 10,"Green",IF('Spans & Layers'[Grade] in {"S1","S2","S3","S4"},"Black","Orange"))))
Tahreem24
Super User
Super User

@rosscortb little confused with your formula pattern. It's better if you could explain in a word or algorithm format. 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 

I think the formula is fine, its getting round this error. error2.PNG

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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