Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi - I currently have a table that is aggregated by date in the columns and a text label as the rows. I have another table (Deals and Coupons) that has those same text labels and returns deal dates according to which items we had on a deal and when. I would like for this formula to return the conditional formatting for every deal that occured in the table, but I have only made it able to work by taking the max aka last deal date in the list. Any ideas how to get around this?
Solved! Go to Solution.
Hi @grbs ,
Try the following code:
Deal format =
VAR tempTable =
FILTER (
'Deals and Coupons',
'Deals and Coupons'[Coupon Start Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
&& 'Deals and Coupons'[Coupon End Date] >= 'Deals and Coupons'[Coupon End Date]
)
VAR MiNIMuMDATE =
MINX ( tempTable, 'Deals and Coupons'[Coupon Start Date] )
VAR MAXIMUMDATE =
MAXX ( tempTable, 'Deals and Coupons'[Coupon End Date] )
RETURN
IF (
AND (
SELECTEDVALUE ( 'Calendar'[Date] ) >= MiNIMuMDATE,
SELECTEDVALUE ( 'Calendar'[Date] ) <= MAXIMUMDATE
),
"#A9F099"
)
In this case for testing I added a new line for the discounts that was in the middle of the period so that I could see if everything was being picked up:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @grbs ,
Try the following code:
Deal format =
VAR tempTable =
FILTER (
'Deals and Coupons',
'Deals and Coupons'[Coupon Start Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
&& 'Deals and Coupons'[Coupon End Date] >= 'Deals and Coupons'[Coupon End Date]
)
VAR MiNIMuMDATE =
MINX ( tempTable, 'Deals and Coupons'[Coupon Start Date] )
VAR MAXIMUMDATE =
MAXX ( tempTable, 'Deals and Coupons'[Coupon End Date] )
RETURN
IF (
AND (
SELECTEDVALUE ( 'Calendar'[Date] ) >= MiNIMuMDATE,
SELECTEDVALUE ( 'Calendar'[Date] ) <= MAXIMUMDATE
),
"#A9F099"
)
In this case for testing I added a new line for the discounts that was in the middle of the period so that I could see if everything was being picked up:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix - thank you so much! This worked perfectly.
Hey @MFelix any idea how I can alter this formula to be used as a column value for a slicer? I would like to have a column that I can slice "coupon" or "no coupon" by.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.