Ignore slicer selection for specific part of Dax measure

[waldnerr]

Hello,

i am using the subsequent measure to filter the TCV from the latest available project evalulation per project within a specific timeframe (year).

Latest TCV =

SUMX (

VALUES ( 'Project Period'[GlobalProjectKey] ),

CALCULATE (

SUM ( 'Project Period'[TCV] ),

LASTNONBLANK ('Date'[Date], COUNTROWS ( RELATEDTABLE ( 'Project Period' ) ) )

)

)

Date table (Datekey = project evaluation periods)

Datekey	Date	Year
08/22	08.08.2022	2022
08/22	10.08.2022	2022
09/22	05.09.2022	2022
...	...	...

Project Period (Fact Table):

GlobalProjectKey	DateKey	Final Acceptance	TCV
A	08/22	2023	1000
A	09/22	2022	1000
A	10/22	2023	1000
A	11/22	2023	1000
B	08/22	2022	4000
B	09/22	2022	4000

It is important, that within the selected timeframe (Year), always the latest evaluation line item is selected.

If i add an external slicer for the "Final Acceptance" date and select "2022", the measure selects the line item for project A with "Datekey" 09/22 and "Final Acceptance" 2022, which doesn't reflect the latest evaluation line item for this project with would be "Datekey" 10/22 and "Final Acceptance" 2023.

I would be happy, if you can show me a way to prevent that the "Final Acceptance" slicer selection influences the selection of the latest evaluation period (datekey per project) the wrong way.

Thank you,

Best regards,

Reinhard

[v-jianboli-msft · Accepted solution]

Hi @waldnerr ,

Please try:

First create a new table for slicer:

Apply the measure to the visual:

Measure =
VAR _a =
    MAXX (
        FILTER (
            'Project Period',
            YEAR ( [DateKey] ) = SELECTEDVALUE ( 'For slicer'[Value] )
        ),
        [DateKey]
    )
VAR _b =
    MAXX (
        FILTER (
            ALL ( 'Project Period' ),
            [GlobalProjectKey] = MAX ( 'Project Period'[GlobalProjectKey] )
                && YEAR ( [DateKey] ) = SELECTEDVALUE ( 'For slicer'[Value] )
        ),
        [DateKey]
    )
VAR _c =
    CALCULATE (
        SUM ( 'Project Period'[TCV] ),
        FILTER ( 'Project Period', [DateKey] = _a )
    )
RETURN
    IF ( _a <> _b, BLANK (), _c )

Turn on show items with no data:

Then create two slicers using 'For Slicer'[Value] and 'Project Period'[Final Acceptance] separately

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @waldnerr ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo 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-jianboli-msft,

thank you for your reply.

Subsequent please find the link to a sample pbix file which hopefully clarifies the scenario:

example.pbix 

Alternative Link 

Thank you,

Best regards,

Reinhard