DAX syntax for applying a filter before a distinct count

[Anonymous]

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

[AlB]

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]

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. 

[amitchandak]

@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))
)