Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi people,
I need you help with the following:
I have a Table in Power BI which shows the following:
now, I want to make a column, which calculates the following logic:
When there has been a visit ("Date of visit (flag)"=1) in a store (in this example "Store 1"), I need to flag the first date AFTER the visit where the Product (in this example "Product 1") is not present ("Product is not present"=1).
I have tried to make the following DAX calculation (Calculated column):
First Instance of "product not present" after "visit" (flag) =
IF(
COUNTROWS(
FILTER(
'Table',
'Table[customer] = EARLIER('Table'[customer]) &&
'Table'[Product] = EARLIER('Table'[Product]) &&
'Table'[Date] <= EARLIER('Table'[Date])&&
'Table'[Date of Visit (flag)]=1
)
) >= 1,
IF(
COUNTROWS(
FILTER(
'Table',
'Table[customer] = EARLIER('Table'[customer]) &&
'Table'[Product] = EARLIER('Table'[Product]) &&
'Table'[Date] <= EARLIER('Table'[Date])&&
'Table'[Product not present (flag)]=1
)
) = 1,1,0))
RESULT:
which is close. But I need a "flag" for EVERY time there has been a visit and the product is not present afterwards. I the calculation I have made it only shows the first time.
What I need is the above example is this result:
one more visit is taking place on the "09-05-2023" and the product is (still) not present, so I need another flag on the date after the visit date, "14-05-2023". And everytime the combination of "visit date" and "product not present" turns up.
I have added a link to a spreadsheet with the table used as an example:
Any help or guidance is REALLY appreciated. Thanks.
Br,
Jayjay0306
Please indicate the expected outcome based on your sample data.
no outcome due to no replies.