Measure in a measure skips row context in report view

[Anonymous]

Hi Community,

I've run into a problem with DAX. Usually I find my answer on this community as millions probably have asked the same question but I can't find it.

Maybe becasue I don't use the right keywords. In any case, I would be very grateful if you could help me.

I'm trying to rebuild a power BI forecast with measures instead of calculated columns as that file became way too big in terms of file size.

I have a dynamic measure ([BL1 Unfunded Old Contracts]) that currently outputs 0,9339 and I want to use that measure in a new measure (Disbursements Next Quarter BL1). This new measure is related to a date table.

If I use the dynamic measure to calculate the new measure it gives me output on a total level but doesn't output the row context I want to see in my Report view. 

Is there any way I can use this measure in my new measure without adding columns or hard coding the output?

Unfortunately I can't share my data as it's my companies data. If necessary I can make dummy data but I figured this question might not require actual data.

DAX formula:

Disbursements Next Quarter BL1 =

VAR _NQ = SELECTEDVALUE( 'Datumtabel'[rYearQuarter] )

VAR Unfunded = [BL1 Unfunded Old Contracts]
RETURN

CALCULATE(

SUMX(Contracttbl;Contracttbl[Commitment left EURO])*Unfunded;

ALL( 'Datumtabel' );

'Contracttbl'[Business line]="Support to early stage finance activities";

TREATAS( { _NQ + 1 }; 'Datumtabel'[rYearQuarter])

)

Result in report view:

SCBD Number	Disbursements Next Quarter BL1
Total	381.709,52

This is correct for the total amount, however I want it to show different SCBD number rows. DAX only gives me different rows in the report view when I write this:

DAX:

Disbursements Next Quarter BL1 =

VAR _NQ = SELECTEDVALUE( 'Datumtabel'[rYearQuarter] )

VAR Unfunded = 0,9339
RETURN

CALCULATE(

SUMX(Contracttbl;Contracttbl[Commitment left EURO])*Unfunded;

ALL( 'Datumtabel' );

'Contracttbl'[Business line]="Support to early stage finance activities";

TREATAS( { _NQ + 1 }; 'Datumtabel'[rYearQuarter])

)

Result in report view:

SCBD Number	Disbursements Next Quarter BL1
SCBD-1018-01	58.275,36
SCBD-1020-01	74.326,85
SCBD-1020-02	0,00
SCBD-1021-01	249.163,88
Total	381.709,52

[Anonymous · Accepted solution]

@saraMissBI @Fowmy . Thanks for your help! I figured it out thanks to your help. The rows that the measure calculated to were no the same rows as the new measure calculated. I ended up making a calculated column in another table to preserve the row context in the new measure.

[saraMissBI]

Hi @Anonymous ,

Have you tried viewing your dynamic measure by SCBD number to make sure your measure slices data by SCBD? Does it return correct numbers for each row? It is better to test your first measure and then tackle the second measure?

Thank you

[Fowmy]

@Anonymous 

Your measure [BL1 Unfunded Old Contracts]  is stored in a variable so it doesn't recalculate within SUMX, call the measure directly win SUMEX like below.

Disbursements Next Quarter BL1 =
VAR _NQ =
    SELECTEDVALUE ( 'Datumtabel'[rYearQuarter] )
//VAR Unfunded = 0,9339
RETURN
    CALCULATE (
        SUMX ( Contracttbl; Contracttbl[Commitment left EURO] ) * [BL1 Unfunded Old Contracts];
        ALL ( 'Datumtabel' );
        'Contracttbl'[Business line] = "Support to early stage finance activities";
        TREATAS ( { _NQ + 1 }; 'Datumtabel'[rYearQuarter] )
    )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn