Help improve my DAX measure - how can I filter my fact to only use latest values from related table?

[alexbjorlig]

I have a data-set, that is connected to a autogenerated calendar. For each year, I would like a distinct count of report keys - but it should only include the latest report per location. Links to resources in the bottom. Dataset looks like this:

Year=2018

The count should here return 2, because it includes Report E (latest for location YYYYY) and Report C (latest for location XXXXX).

Year=2019

The count should here return 2. Report B latest for location XXXXX and D latest for location YYYYY.

 

Year 2020

The count should here return 2. Report A latest for location XXXXX and D latest for location YYYYY.

Attempt so far

CALCULATE(
    DISTINCTCOUNTNOBLANK('Fact'[ReportKey])
    ,REMOVEFILTERS('Calendar')
    ,'Calendar'[Date] <= MAX('Calendar'[Date])
    ,FILTER(
        ALL(ReportDim),
        VAR LOC = ReportDim[Location] RETURN
        VAR LATEST = CALCULATE(MAX(ReportDim[PublishDate]), ReportDim[Location] = LOC) RETURN
        ReportDim[PublishDate] = LATEST
    )
)

But the measure does not work - it does not successfully filter to only latest reports.

Please find Power BI report is here, and you find the dataset here.

[alexbjorlig · Accepted solution]

After some (more) research, I think this is the best solution (in my original power bi file we use bi-directional filters, and the solution utilizes that fact):

VAR ONLY_LATEST = CALCULATETABLE(
    FILTER(
        ReportDim,
        VAR LOC = ReportDim[Location] RETURN
        VAR LATEST = CALCULATE(MAX(ReportDim[PublishDate]), REMOVEFILTERS(ReportDim), ReportDim[Location] = LOC) RETURN
        ReportDim[PublishDate] = LATEST
    )
    ,REMOVEFILTERS('Calendar')
    ,'Calendar'[Date] <= MAX('Calendar'[Date])
)
RETURN CONCATENATEX(ONLY_LATEST, ReportDim[ReportKey])

[alexbjorlig]

The solution seems to be pretty close, but does not give correct result when there is a slicer on reports, check image:

[wdx223_Daniel]

not sure this is really what you want, buy it feedback the correct result ,lol

_m4 =
VAR _MaxDate =
    MAX ( 'Calendar'[Date] )
VAR _LastDates =
    FILTER (
        'ReportDim',
        'ReportDim'[PublishDate]
            = CALCULATE (
                MAX ( 'ReportDim'[PublishDate] ),
                'ReportDim'[PublishDate] <= _MaxDate,
                ALLEXCEPT ( ReportDim, ReportDim[Location] )
            )
    )
VAR _t =
    CALCULATETABLE (
        FILTER (
            'Fact',
            CONTAINS (
                _LastDates,
                ReportDim[ReportKey], 'Fact'[ReportKey],
                ReportDim[PublishDate], 'Fact'[Date]
            )
        ),
        'Calendar'[Date] <= _maxdate
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Fact'[ReportKey] ),
        _t,
        'Calendar'[Date] <= _maxdate
    )