Help needed with DAX Switch Statement multiple columns (Picture included)

[yuj178]

Resolve, thanks a lot

@HotChilli 

[HotChilli]

Suggest you create the table I suggested (Region, Lower Limit, Upper Limit, Rating) for one region as a test. This will have 5 lines in it.  Just type it in in 'Enter Data'

Then, in the other table, create a column which returns the rating.

Obviously if you get it working it will only return a rating for the region you have in the first table but you can then expand the first table to cover all regions.

Here is a similar post

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d8e66abd-c383-4234-a8cf-8406bbe64ce2/if-dax... 

. Look at the DAX in the answer post . 

Yours may look something like this.

Let me know how it goes.

[yuj178]

Hi @HotChilli thanks for that but I am not sure what you mean by having a lower and upper limit column capturing the 5 different performance bands, i.e. > 30, >25 and <=30 etc.

Sorry I am new to PowerBI

[HotChilli]

Regionlowhighgrading

West	35	1000	outstanding
West	30	35	vs
West	25	30	s
West	20	25	u
West	0	20	p

Each row shows lower, upper and the grading  

[yuj178]

I think I will abandon doing it using the big SWITCH approach, as the circular dependency error is just getting too difficult. 

Just needed to clarify that each line within SQL server represents an individual saleperson's stats for an indivudal day, ie sale made for the whole day, total amount of calls for the day etc. Right now I am looking at an aggregated monthly view and have a column that is the summation of everdays sale ammount so I can view it at a monthly view. Using the aggregated monthly sales figure, I just need help with finding the corresponding grading provided by HR.

If you or someone can please help with teaching me how to link up a performance grading table, and then using the [Sales Amount] month CALCULATED MEASURE to look up the corresponding grading that would be fantastic. 

😢😢

[HotChilli]

That's what I've tried to give you (a better way).

If you go down the route of having a big switch statement (and it will be big), all the business logic will be transferred from your pdf table to a complex dax statement.

If you follow what I suggested, you will have a table with all the logic included and one relatively straightforward dax statement.

You can create the table from the pdf in Power Query if you have the skills or type it in Excel or directly in PowerBi via Enter Data

[yuj178]

Thanks a lot @HotChilli, I have tried doing it using the large SWITCH statement and whilst the syntax is correct, its is now coming up with an error of 'cicurlar dependency'.

My DAX statement reads as

Grading = SWITCH (

    TRUE(),

('table'[Regions]) = "West" && [Sales amount for the month] > 35, "Outstanding",

('table'[Regions]) = "West" && [Sales amount for the month] > 30 && [Sales amount for the month] <= 35, "Very Satisfactory",

('table'[Regions]) = "West" && [Sales amount for the month] > 25 && [Sales amount for the month] <= 30, "Satisfactory",

....

I am guessing the circular dependency error is because the 'Sales Amount for the month' column is a CALCULATED MEASURE and PowerBI doesn't know it needs to computer [Sales Amount for the Month] before running the SWITCH statement. 

I am keen to try try doing it using your method as the DAX formula will be  alot cleaner but I am not too sure how to go about doing it because conditions uses involves ranges and it is not a static number and thats what makes it so much more difficult. Is it just a case of creating an additional table and then using a lookup function? Any materials I should look to?

Really appreciate your help so far, this is actaully a lot more challenging than I anticipated it to be 

[HotChilli]

The first table should be remodelled to be :

Region, Lower Limit, Upper Limit, Rating.

You can then write a measure or column to retrieve the Rating based on Region, lower limit < Sales and upper Limit >= Sales

[yuj178]

@HotChilli Just wanted to clairfy that the first table is not actually a table in SQL server, its like a seperate PDF document by the guys in HR.

I have experimented with the following DAX code, but it doenst seem to work

Grading = 

SWITCH (

          TRUE(),

          Sheet1[Regions] = "West" && Sheet1[Sales Amount for the Month] > "30" && Sheet1[Sales       Amount for the Month] <= "35", "Very Satisfactory" 

Sheet1[Regions] = "West" && Sheet1[Sales Amount for the Month] > "25" && Sheet1[Sales       Amount for the Month] <= "30", "Satisfactory" 

The statements dont seem to work and will take a while to be entered, I just wanted to know if there are any issues with teh SWITCh statements are written or if there is a better way to do this rather than typing it out the conditions manually.