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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Gr8apmech
Helper I
Helper I

Switch True() Leaving Blanks - Is there a better way?

My data is for supplier performance.  I have a measure to determine Defect per Unit (DPU) and a measure to determine Supplier On Time Delivery (OTD).  Using those 2 measures, i'm attempting to apply a scorecard which applies both color and 'text' to the table.  My problem is the Switch True method looks at the data in a very specific order whereas if my supplier has zero defect (DPU=0) and delivery is not on time (OTD) it is not considering the supplier as "Unsatisfactory".

 

Measure:

Supplier Rating = SWITCH(TRUE(),
[DPU]>10000&&[Supplier OTD]<0.98,"Unsatisfactory",
[DPU]>1000&&[DPU]<10000&&[Supplier OTD]>0.98&&[Supplier OTD]<0.985,"Marginal",
[DPU]>100&&[DPU]<1000&&[Supplier OTD]>0.9851&&[Supplier OTD]<0.995,"Fair",
[DPU]>10&&[DPU]<100&&[Supplier OTD]>0.9951&&[Supplier OTD]<0.999,"Excellent",
[DPU]<10&&[Supplier OTD]>0.9991&&[Supplier OTD]<=1,"Best in Class")

 

What my data looks like:

Supplier RatingSupplierDPUSupplier OTD
Best in ClassA.H. 0100.00%
Best in ClassAdvan0100.00%
 Best 142857.1100.00%
 Globa166666.7100.00%
 WEETE166666.7100.00%
 AT In250000100.00%
 TSI250000100.00%
 Sager333333.3100.00%
UnsatisfactoryNewar13888.989.60%
 Amazo1424.589.30%
UnsatisfactoryImagi35714.389.30%
 Austr088.90%
 The B088.90%
UnsatisfactorySteve333333.366.70%
 Sigma050.00%
 Teled050.00%
UnsatisfactoryETS-L25000050.00%
UnsatisfactoryBAUGH50000050.00%
 Laco 033.30%
UnsatisfactoryNORMA111111.133.30%
UnsatisfactoryParag50000033.30%
 Rothe030.10%
UnsatisfactoryProdu74074.122.20%
 Cirri020.00%
UnsatisfactoryGretn30000020.00%
 DMC T015.00%
UnsatisfactoryMoss 142857.114.30%
 Extre08.30%
UnsatisfactoryPelic20000000.00%

 

What I would like to see:

All rows filled in with a Supplier Rating based on this 'scale' (I understand many of the 'supplier' do not meet the Marginal, Fair, Excellent categories yet):

 

Supplier Rating DPU OTD Dashboard Color

  • 5. Unsatisfactory >10k <98% Red
  • 4. Marginal 1,000-10K 98-98.5% Amber
  • 3. Fair 100-1000 98.51-99.5% Yellow
  • 2. Excellent 10-100 99.51-99.9% Green
  • 1. Best in class <10 99.91-100% Blue
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Change the order, and close the gaps.

 

Supplier Rating = 
SWITCH(TRUE(),
[DPU]<10&&[Supplier OTD]>0.999,"Best in Class",
[DPU]<100&&[Supplier OTD]>0.995,"Excellent",
[DPU]<1000&&[Supplier OTD]>0.985,"Fair",
[DPU]<10000&&[Supplier OTD]>0.98,"Marginal",
"Unsatisfactory")

View solution in original post

2 REPLIES 2
v-kongfanf-msft
Community Support
Community Support

Hi @Gr8apmech ,

 

Do you want a result similar to the following, you can use the following formula to create a calculated column.

Supplier Rating =
SWITCH (
    TRUE (),
    [DPU] > 10000
        && [Supplier OTD] < 0.98, "Unsatisfactory",
    [DPU] > 1000
        && [DPU] <= 10000
        && [Supplier OTD] >= 0.98
        && [Supplier OTD] < 0.985, "Marginal",
    [DPU] > 100
        && [DPU] <= 1000
        && [Supplier OTD] >= 0.9851
        && [Supplier OTD] < 0.995, "Fair",
    [DPU] > 10
        && [DPU] <= 100
        && [Supplier OTD] >= 0.9951
        && [Supplier OTD] < 0.999, "Excellent",
    [DPU] <= 10
        && [Supplier OTD] >= 0.9991
        && [Supplier OTD] <= 1, "Best in Class",
    "Unrated"
)

vkongfanfmsft_0-1726480086379.png

 

Best Regards,
Adamk Kong

 

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

lbendlin
Super User
Super User

Change the order, and close the gaps.

 

Supplier Rating = 
SWITCH(TRUE(),
[DPU]<10&&[Supplier OTD]>0.999,"Best in Class",
[DPU]<100&&[Supplier OTD]>0.995,"Excellent",
[DPU]<1000&&[Supplier OTD]>0.985,"Fair",
[DPU]<10000&&[Supplier OTD]>0.98,"Marginal",
"Unsatisfactory")

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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