Forum Discussion
IF formula with multiple conditions data from multiple tables
Hi all,
Can someone help me to figure out what formula and DAX combination to get the value?
Background
This is a human resource dashboard. We are going to identify how we are performaning in different locations. We have two dataset table and one reference table.
Dataset Table
Above are my dataset. The first one reflects candidates in which date in which location. The second table is generated from the first table to extract how many candidates in each location.
Reference or Lookup Table
Above is my lookup table to identify the performance statuses of every location. The figure is target of candidates. Green is a pass and the best scerarios based on the date and target. If it is before 24 May, and we have already got more than 2 people in the location, it means we pass the test it is green. If it is after 30 May, we got more than 6 people in this area, we are also the Green. Amber is a warning that is may turned to Red. The target in this colour is a range for every location. For example, if we got between 1 and 2 people before 24 May, we are in Amber. Red is the worst case means we may fail to achieve the target. If before 24 May, we did not have one candidate in this location, this location is Red. After 30 May, if we got less than 4 people in this area, this area are in poor performance.
The Value we want to get
We want to get the value of the No. of Location ID during the different period in each colour. What I am going to do is first to create a table for different period, they are before 24 May, 25 May to 26 May, 27 May to 29 May and after 30 May. I want to get what is color for each Location ID during these periods and finally I can get how many location ID in this color during this period? Is it possible to use any combination of DAX or IF statement to create the table and get the value
Thanks in Advance
4 Replies
Dear Friend,
We can do it in DAX, Please refer the below formula
No. of Location in each date in each colour. =
Switch ( True,
Date < '24-May-2020' && People >2 , "Green",
add condition for red and Yellow etc.,
)
do with switch we can acheive this scenario.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
http://powerbi.baskarbiconsultant.com/Hi Baskar,
Thanks for your reply. Can you please elaborate this solution a little bit more? Using this SWITCH dax we can get the color and how I can get the how many of location IDs in this color during this time period? It might be my problem let me clarify my logic more. What I am going to do is to create a table for different period, they are before 24 May, 25 May to 26 May, 27 May to 29 May and after 30 May. I want to get what is color for each Location ID during these periods and finally I can get how many location ID in this color during this period?
Regards,
Refer, these. You can create measure on the bucket.
https://community.powerbi.com/t5/Desktop/Group-Measure-values-on-rows/td-p/514475
https://community.powerbi.com/t5/Desktop/Dax-query-using-calculated-buckets-as-filters/td-p/437466
You can create a color measure and use that in Advance condition or conditional formatting
Color Brand = if(FIRSTNONBLANK('Item'[Brand],BLANK())="Brand 11","red","green")
Color scatter = if(SUM(Sales[Net Sales])>2500 && SUM(Sales[Margin])>400,"green","red")You can combine the parameter and measure and create as per need.Hi
Thanks for reply and providing me the useful links. The logic is quite similar to my case now but I still can not figure out how to apply in my case? Let me clarify my case and the logic a bit more. What I am going to do is to create a table for different period, they are before 24 May, 25 May to 26 May, 27 May to 29 May and after 30 May. I want to get what is color for each Location ID during these periods and finally I can get how many location ID in this color during this period? Can you please help me apply those formulas to my case?
Kind Regards,
