Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX syntax for applying a filter before a distinct count

Hi All, 

 

I have been racking my brains with this one, I am trying to write a measure that brings me an accumulative line graph on a fixed date. This will sit inside a visual that is dynamic (eg two more lines that show whichever date is selected). 

 

The problem I seem to have is my formula calculates the distinct count BEFORE it applies the date filter. This results in skewed data being returned.

 

My formula:

"Memphis Original Plan =
CALCULATE(
CALCULATE(
CALCULATE(
DISTINCTCOUNT('Project Plan Table'[PROJECT_PLAN])
),
FILTER('Project Plan Table','Project Plan Table'[QB_Date] = DATE(2020,08,03))
),
ALL('Project Plan Table'[QB_Date]),
FILTER(ALL('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) )
)"

 

I have tried lots of variations but with no success. 

 

Is there something here that I haven't considered?

 

Thank you, 

Ben

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Try like

    Memphis Original Plan =
    CALCULATE(
    DISTINCTCOUNT('Project Plan Table'[PROJECT_PLAN]),FILTER(ALLselected('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) )
    FILTER('Project Plan Table','Project Plan Table'[QB_Date] <= DATE(2020,08,03))
    )

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    What is the logic for using so many, nested, CALCULATE()s? I'm not sure I understand what you need, perhaps you could explain it a bit more with an example, inlcuding the fields being used in the visual. Would something like this not suffice?

    Memphis Original Plan =
CALCULATE (
    DISTINCTCOUNT ( 'Project Plan Table'[PROJECT_PLAN] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hey, AlB  and amitchandak,

       

      Thank you for coming back to me so quickly. This is quite a large and complex dataset (800Mb ish?) so unlikely I can find a way to trim down for sampling, although the data isn't sensitive so it's not impossible, I will look into it. 

       

      Using a long workaround of referencing the table the visual should look like this:

      Where the slicer represents a timestamp in the programme database, the red must remain the same, the purple and orange change accordingly. 

       

      At a push, I can stay with this solution, but I am certain a smoother process can be built. 

       

      The nested calculates are an attempt to perform the distinct count before the filter, also where the FILTER(ALL('Calendar'),'Calendar'[Date] <= MAX('Calendar'[Date]) ) places in the code decides whether the line accumulates the total or not.