Ignore visual filter within measure calculation but apply filter later onto result

[waldnerr]

Hi,

i have created a measure, which provides me the latest evaluation data for a specific project. The measure below shows me for example the latest TCV:

Latest TCV =
SUMX (
VALUES ( 'Project Period'[GlobalProjectKey] ),
CALCULATE (
SUM ( 'Project Period'[TCV] ),
LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( 'Project Period' ) ) )
)
)

Example Data in "Project Period" table:

* 'Date'[Date] has a 1:n connection to "Project Period.DateKey"

If i use the measure withouth a filter, it selects the correct "TCV" from the line item highlighted in green in the example data shown above.

I would like to select the "TCV" of all projects, where the "qg13date" is in the year 2020, based on the latest "DateKey". This would mean, that this project would need to be completely excluded, because the lastest "qg13date" is on 23.04.2021.

If i just set a filter for "qg13date" to 2020, this doesn't work, because the measure selects the line item with the latest "qg13date" in 2020 (highlighted in red).

I would highly appreciate i you could help me to adapt this measure accordingly to find a solution for this problem (to make the measure able to select the latest dataset first, before external filters interfere.

Thank you,

Best regards,

Reinhard

[v-yingjl]

Hi @waldnerr ,

To get the latest date value, you can create this simple measure:

Latest TCV =
CALCULATE (
    SUM ( 'Project Period'[TCV] ),
    FILTER (
        ALL ( 'Project Period' ),
        'Project Period'[DateKey] = MAX ( 'Project Period'[DateKey] )
    )
)

Use a card visual to show the result and disable the interaction with the Year slicer:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[waldnerr]

Hi @v-yingjl,

thank you for the quick response. Unfortunately, the proposed solution doesn't create the desired result.

The goal is to see the "TCV" for each "GlobalProjectKey" based on the line item with the latest (max) "Datekey", where "qg13date" for this selected line item must be in year 2020 based on the selection in the slicer.

Based on the provided exemplatory data in my recent post, the result would be blank(), because the "qg13date" for the dataset with the latest (max) "Datekey" is in 2021 (see highlighted in green in the example data).

The solution (where i don't know how to program it) would be to select at first the TCV for each GlobalProjectKey based on the latest Datekey and apply the qg13date filter later via slicer ,when the prior calculation is already completed.

Best regards,

Reinhard

[Anonymous]

You could use the USERELATIONSHIP function in your CalculateArgument. Set an inactive relationship from Dates[Date] to qg13Date in the Model, then add USERELATIONSHIP(Dates[Date], Table[qg13Date]) as your last argument in CALCULATE. Should work!