Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
I have 2 measures :
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...
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
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.
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
@jct999
you need to read about Autoexist. Long story short, you need a date table. Just a simple one with date, year and month.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |