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
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

I have test on my side by these three formula

Point 1 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event A- Actual",
    FILTER (
        ALL ( DIM_DATE ),
        DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )
    )
)
Point 2 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event B- Forecast",
   FILTER (
       ALL ( DIM_DATE ),
        --DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )&&
            DIM_DATE[FINANCIAL_YEAR] = MAX ( DIM_DATE[FINANCIAL_YEAR] )
    )
)
Point 3 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event B- Forecast",
   FILTER (
       ALL ( DIM_DATE ),
        DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )&&
            DIM_DATE[FINANCIAL_YEAR] = MAX ( DIM_DATE[FINANCIAL_YEAR] )
    )
)

Result:

and here is my sample pbix file, please try it.

 

If not your case, Please share your sample pbix for us. You can upload it to OneDrive and post the link here.

 

Best Regards,

Lin

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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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. 

  • 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