topic Re: Incorrect result with nested measure in DAX Commands and Tips

Incorrect result with nested measure

jct999 — Thu, 02 Jun 2022 06:05:44 GMT

Hi All

I have 2 measures :

IMPORTER_MARKET_ACTUAL
IMPORTER_MARKET_ACTUAL_CY_MTD that references IMPORTER_MARKET_ACTUAL. The purpose of this 2nd measure is to restrict the time scope of the first measure.

Problem : The 2nd measure is not computed correctly. Time criterias seem not to be applied to the nested measure IMPORTER_MARKET_ACTUAL.

Can any one help me to understand where is the issue ?

Thanks

IMPORTER_MARKET_ACTUAL :=
SUMX (
     VALUES ( 'COUNTRY'[COUNTRY] );
     VAR registration =
          SUMX (
               CALCULATETABLE (
                   'DATASET';
                   'DATASET'[KPI_TYPE] = "REGISTRATION";
                   'DATASET'[KPI_NATURE] = "ACTUAL";
                   IMPORTER[IMPORTER] = "I"
               );
               'DATASET'[VOLUME]
         )
VAR market =
          SUMX (
               CALCULATETABLE (
                    'DATASET';
                    'DATASET'[KPI_TYPE] = "MARKET";
                    'DATASET'[KPI_NATURE] = "ACTUAL";
                    ALL ( 'DATASET'[BRAND] );
                    ALL ( IMPORTER )
               );
               'DATASET'[VOLUME]
          )
)
RETURN
     IF ( registration > 0; market; BLANK () )

It returns 16 744 921

IMPORTER_MARKET_ACTUAL_CY_MTD :=
VAR cy = 2022
VAR cm = 4
VAR r = CALCULATE(
     [IMPORTER_MARKET_ACTUAL];
     'DATASET'[YEAR] = cy;
     'DATASET'[MONTH] <= cm
RETURN r

It returns 16 744 921. It is incorrect, it should return 1 541 374...

Re: Incorrect result with nested measure

tamerj1 — Thu, 02 Jun 2022 08:58:24 GMT

Hi jct999
Just use a date table and all will be as you wish.

You may try adding REMOVFILTERS ( DATASET ) to 2nd Measure CALCULATE but I don't think it is going to work properly.

Re: Incorrect result with nested measure

jct999 — Thu, 02 Jun 2022 09:40:05 GMT

Hi tamerj1

Thanks for your reply,

I'm not sure that adding a Date table to the model will solve the problem, because the YEAR and MONTH columns are not strictly speaking time dimensions for the model. They could be replaced by other things, such as WEIGHT and SIZE... and the problem remain the same.

Using REMOVEFILTERS will remove all the outer filters that are applied in the visual (i.e on DATASET[COLOR], DATASET[PRICE_CATEGORY] ... etc), and it will produce incorrect results

It seems that the filters applied in the 2nd measures are not propagated within the nested measure (1st measure) ; and I dont't understand why... May be because :
- Filters in 2nd measure are not propagated into the VAR in the 1st measure ... ?

- There are to much nested calculations in the 1st measure : SUMX( SUMX ( CALCULATETABLE ( ... ) ) ... ?

- Context transition or iterator issue/poor understanding ... ?

DAX is complex to use !
Need help

Thanks

Re: Incorrect result with nested measure

tamerj1 — Thu, 02 Jun 2022 09:38:18 GMT

jct999
you need to read about Autoexist. Long story short, you need a date table. Just a simple one with date, year and month.

Re: Incorrect result with nested measure

Anonymous — Wed, 22 Jun 2022 09:32:38 GMT

Hi jct999 ,

Please modify the IMPORTER_MARKET_ACTUAL_CY_MTD.

IMPORTER_MARKET_ACTUAL_CY_MTD := VAR cy = 2022 VAR cm = 4 VAR r = CALCULATE ( [IMPORTER_MARKET_ACTUAL], FILTER ( ALL ( DATASET ), 'DATASET'[YEAR] = cy && 'DATASET'[MONTH] <= cm ) ) RETURN r

If I have misunderstood your meaning, please provide some sample data without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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