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

[Ricardo77]

Hello,

Im working on a report where 2 Factual[IDMoment]'s are selected on the report filters, example 1000 and 1500.

From these IDs i have to show, for example, a Dimension Label1 in the rows, and some values coming from the DW, and then i need to calculate a Delta between the 2 IDs on the report.

So I use a matrix:

(1st of all: Is there any better visual to do this? Or a way to hide columns in Power BI?)

Im trying to control this using variables, to develop a good performing code and of course avoid doing unnecessary calculations

The idea is to avoid doing the calculation on delta2 of previous_idmoment "1000", and just showing blank () or zero

RESULT  = 
VAR SELECTED_2_IDs = ALLSELECTED(FACTUAL[IDMOMENT])
VAR LATEST_IDMOMENT =   CALCULATE(MAX(FACTUAL[IDMOMENT]); FACTUAL[IDMOMENT] IN SELECTED_2_MOMENTS) 

VAR PREVIOUS_IDMOMENT = CALCULATE(MIN(FACTUAL[IDMOMENT]); FACTUAL[IDMOMENT] IN SELECTED_2_MOMENTS)

RETURN IF (LATEST_IDMOMENT <> PREVIOUS_IDMOMENT; 
            ...;
            BLANK();)
 

This was working for a specific measure but i've done something and it stopped working.... 

Anyway, my question is what is the best way to control "where" calculations should be done and so, how to identify context of a column (or cell)

Thanks 

Regards

[MFelix]

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.

[Ricardo77]

hello @MFelix  , what a great piece of DAX code, Jesus  😂!

I'll try it out and tell something after.

Please allow me just a few comments & outburts ("desabafos", as we say in portuguese):

1- DAX is not easy, this doesn't help end users (namely business users for self service BI...)

2- DAX must be carefully developed, or else is very slow, (so the perspective "let's try to see if this works" many times results in slow reports...)

I end with a question, related with the 2nd point:

is there anyway (considering your code or generally speaking) to refer a (previous) column in a table or matrix, to optimize calculations?

It could be like picking the delta you calculated, and using it in a complex calculation like

( row_columnA * row_columnB / row_columnZ) + (row_columnD *delta)   

without having to calculate delta it again.

Thanks again, Regards.

[MFelix]

Hi @Ricardo77 ,

Totally agree with you on the two points you refer, check this post from the great Alberto Ferrari where we defend that DAX is simple, but it is not easy.

https://www.sqlbi.com/blog/alberto/2020/06/20/7-reasons-dax-is-not-easy/

Regarding your question this is possible to have using the DELTA has a measure within a measure or making a temp_table. But if you could give some more information about the context that you need, because using measures depends on context also.

[Greg_Deckler]

@Ricardo77  - You can use a custom matrix hierarchy to "hide" columns. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

Not sure I am clear on requirements. Can you post sample data as text?