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 records events which occur against a sale to track dates. 

 

Sale Event

Sale Event IDSale IDSale Stage EventEvent TypeEvent Date
11Event A - ActualActual01/06/2018
21Event B - ActualActual01/07/2018
31Event A- ForecastForecast01/08/2018
41Event B- ForecastForecast01/09/2018
52Event A- ActualActual01/10/2018
62Event B- ActualActual01/11/2018
72Event A- ForecastForecast01/12/2018
82Event B- ForecastForecast01/01/2019
93Event A- ActualActual01/02/2019
103Event B- ActualActual01/03/2019
113Event A - ForecastForecast01/04/2019
123Event B- ForecastForecast01/05/2019

 

The date column is joined to a date dimension which contains a week ending date column and a year column. 

The report has drop downs which the user can select to view the sales activity for any given week in a year. 

 

Example parameters in the Power BI Report:
Week Ending:27/01/2019
Year:2019

 

The three measures I need to calculate are below. Points one and two I have been able to solve, but point 3 has me stumped. 

 

  • No. of Sales where event A has actually occurred to Date:

= 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])))

 

Result from example = 2

 

  • No. of Sales where event B is forecast to occur this year:

= 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 from example = 2

 

  • No. of Sales where event A has actually occurred to date and event B is forecast to occur this year. 

The result from example would be 1 sale. Sale ID 2. Its the only sale where event A had actually occured before the week ending date and that Event B was forecast to occur before the end of the year. 

 

I've not been able to acheive this within the CALCULATE / FILTER approach and searching online has been fruitless.

 

Any help would be greatly appreciated. 

 

Need Help

6 Replies

  • 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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Share the link from where i can download your PBI file.  If you cannot do so, then share the Dim_Date Table with the Date, Week and Year columns.