Forum Discussion
Help needed with DAX Switch Statement multiple columns (Picture included)
Community ChampionThat'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
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
