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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JesperBT
Frequent Visitor

Single column with multiple IF's returning first correct result

Hello everyone,

 

I'm making a tool that will be used to pinpoint where to focus our efforts and here I would like to create a form of check list.

 

Basically I have 5-6 criteria which each returns a different reason and depending on which reason gets "TRUE" first, that is the reason I want to show in the column. When the issue has been fixed/corrected and the data is refreshed the day after, then it will most likely be a new reason that shows. I was thinking of making 5-6 conditional columns, one for each "check", "1" is true and "0" is false. Then in the end I would make another column to look at each of the other 5-6 columns for the first one to contain the number 1 and then hard code the reason name in this column.

 

But I was thinking, if it was possible to optimize the solution, so instead of having 5-7 conditianl columns, I could maybe create a single one that does it all?

 

First: IF column "X" = null then "Y" (If this column is blank/null then this is the first criteria)

Second: IF column "X" is the same or larger then "Y" then "Z" (If the number of one column is larger than anther, then this is true)

Third: IF column "X" is less than "Y" then "Z" (same as above, just the other way around)

Forth: Avg_Sales divided with 7 (to get Avg_Sales per day) The new "Avg_Sales per day" diveded by "Y" = "Z". IF "Z" is under "3" then this criteria is true.

Fifth: IF "Forecast" is 60% or lower than "Avg_Sales" then this criteria is true.

Six: IF none of the above is true, then this is the result "No reason found"

 

I don't know what the best practise is, but if you already know, that this solution would be much worse than doing one at the time, then please let me know and also if you know the best place to create this column, would it be best to make it inside the query editor or in the table view or maybe (...if possible) as a measure?

 

Thanks in advance,

B

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors