Forum Discussion
Help with DAX Filtering
Thank you both for your help.
I've created an example .pbix.
The solutions provided are useful, but dont quite meet the requirement on point 3.
Essentially I believe that point 3 is intersect of point 1 and 2. My DAX terminology isnt too hot so its hard to describe. This pseudo sql might help.
SELECT COUNT(DISTINCT SALE_ID)
FROM SaleEvent
INNER JOIN DIM_DATE
ON SaleEvent.Date = DIM_DATE.date
WHERE (SaleStageEvent = 'Event A - Actual' AND DIM_DATE.WEEK_ENDING <= @Weekending)
OR
(SaleStageEvent = 'Event B - Forecast' AND DIM_DATE.Year = @Year)
Its the number of sales where both Event A has actually occured in the past and Event B is forecast to occur in the year.
Hope that helps clarify and many thnaks fro your help.
Hi,
In your initial post, you mention that the answer for measure1 should be 2 - how? I think it should be 1. Sale ID3 registered "Event A- Actual" on or before 27 January 2019. if you want to ignore the Year 2019 and look at all "Event A- Actual" on or before 27 January 2019 then the answer should be 3 (ID1, ID2 and ID3). I cannot understand the answer as 2 for measure 1.
Could you kinldy review your source dataset and your expected result? Share the revised dataset and the expected result.
Thanks for your response and apologies for the delay in this response.
I havent made any changes, but I've filtered the table to show how I think the answer to measure (1) should be 2.
On two occasions event A actually occured before 27/01/1019.
Hope that helps clarifiy.