Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help with DAX Filtering

Fairly new to DAX and I'm having a hard time wrapping my head around how to use filters in the following scenario. Any help would be greatly appreciated.    I have a table like the one below which ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you both for your help. 

 

I've created an example .pbix. 

 

https://cpplc365-my.sharepoint.com/:u:/g/personal/jon_murphy_cpplc_com/EZs1zmDIuDdNkoqmLGrQdu4BWgTCj-w3o4w5O_CW_8rBYQ?e=cnW4QI

 

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. 

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

I use this formula for point 3 in your sample report, it works well.

3) No. of Sales where event A has actually occurred to date and event B is forecast to occur this year = 
CALCULATE (
    DISTINCTCOUNT ( 'Sale Event'[Sale ID] ),
    'Sale Event'[Sale Stage Event] = "Event B - Forecast",
   FILTER (
       ALL ( DimDate ),
        DIMDATE[WEEK_ENDING] <= MAX ( DimDate[WEEK_ENDING])&&
            DimDate[YEAR]= MAX ( DimDate[YEAR] )
    )
)

Why it doesn't  quite meet the requirement on point 3?

 

Best Regards,

Lin