Identify (filter) context of a table column to avoid unneeded calculations

Hi Ricardo77 ,

Believe this can be done in the following way.

• Create a disconnected table with the following code:

IDMOMENT = UNION(VALUES(FACTUAL[IDMOMENT]); {( "Delta")})

This create a table with all the IDMoments and an additional line with the value Delta

• Add the following measure:

SUMOFVALUES =
VAR MAXIMUM =
    MAX ( FACTUAL[IDMOMENT] )
VAR minimum =
    MIN ( FACTUAL[IDMOMENT] )
RETURN
    SWITCH (
        SELECTEDVALUE ( IDMOMENT[IDMOMENT] );
        "Delta";
            CALCULATE (
                SUM ( FACTUAL[DW] );
                FILTER ( FACTUAL; FACTUAL[IDMOMENT] = MAXIMUM )
            )
                - CALCULATE (
                    SUM ( FACTUAL[DW] );
                    FILTER ( FACTUAL; FACTUAL[IDMOMENT] = minimum )
                );
        CALCULATE (
            SUM ( FACTUAL[DW] );
            FILTER ( FACTUAL; FACTUAL[IDMOMENT] = MAX ( IDMOMENT[IDMOMENT] ) )
        )
    )

• Add a slicer with the column FACTUAL[IDMOMENT]
• Create your matrix with the following setup:
  • Rows: Factual[dimension]
  • Columns: IDMOMENT[IDMOMENT]
  • Values: [SUMOFVALUES]

As you can see below the column Delta is on the end of the data and no need to hide it on the several other columns, be aware that this solution has two limitations:

• IDMOMENT needs to be in text (because of the delta)
• The calculation is only on MAX and MIN values selected so if you select more IDMOMENTS they will only consider max and minimum values and not the values in between.

If you want to overcome those measures needs to be changed a little bit.

Check PBIX file attach.