wrong values with customized double header in matrix

Question

I use a custom calculated table for the header which was an answer of a my previous question: https://stackoverflow.com/a/61469905/5950313

The measure AN is calculated within the following script: The goal of the dimensionmeasure is to calculate the count of rows from fact_an cumul of 12 months where Fact_AN[Vitesse_Transf_Mois]<= SELECTEDVALUE(Dim_VieillissementAN[ID_Tranche])

  AN = 

VAR a = SELECTEDVALUE(Dim_DateFicheAgent[ID_DateFicheAgent])
VAR b =SELECTEDVALUE('Seniority banking'[banking seniority])
RETURN

CALCULATE(
COUNTROWS(FILTER(Fact_AN;

     (Fact_AN[banking seniority]<=b && NOT ISBLANK (Fact_AN[banking seniority]))));
         DATESBETWEEN (
        Dim_DateFicheAgent[ID_DateFicheAgent];
        NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] ) ));
        LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] )

))

the measure DimensionMeasure returns wrong values, it's almost the same value for all middle in the matrix.

How to correct it?

I use a star schema which means; I have only one fact table fact_an. The table fact is linked to dim_produit by code_produit. The description of the table dim_produit:

Codeproduit Dim5Rapport   Dim6rapport

I try

   Formules = 
VAR Top1 = SELECTEDVALUE ( EnteteRapportAgentClient[Top] )
VAR Middle = SELECTEDVALUE ( EnteteRapportAgentClient[Middle] )
VAR BottomIndex = SELECTEDVALUE ( EnteteRapportAgentClient[Index3] )
VAR Val =
    SWITCH (
        TRUE ();
        Top1 = "Nombre de leads"; [Lead]+ 0;
        Top1 = "Affaires nouvelles" && BottomIndex <> 0; [AN]+0;
        Middle = "Total AN";[AN]+ 0;
        Middle = "Taux Transfo"; DIVIDE([AN];[Lead])
    )
VAR ValF=

     IF(   Middle = "Taux Transfo";
        FORMAT ( Val; "0.0%" );
        FORMAT ( Val; "0" ))
VAR Val2=
     IF (ValF="0";"";ValF
    )RETURN Val2

But it returns always error. I put an example here https://drive.google.com/file/d/1i5HEnpoJ5mgEl98xUZzPFo7D6S0C-_tm/view?usp=drivesdk

The wrong values is for AN it returns the same value everywhere

here are the expected results:

are you aware that when you export the data is incredibly messy? — Seymour
and also if you need to add a category or rename one you need to manually modify the model and measures — Seymour
It's hard to even read this question much less answer it. Please clean it up before expecting people to try to answer. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-09T15:14:07

The key here is that you need to pass the header context as filter context to your measure.

For example, instead of the line

Top1 = "Affaires nouvelles" && BottomIndex <> 0, [AN]+0,

You need to replace that [AN] with

CALCULATE ( [AN], Dim_Produit[Dim5Rapport] = Middle, Dim_Produit[Dim6Rapport] = Bottom )

where

VAR Middle = SELECTEDVALUE ( EnteteRapportAgentClient[Middle] )
VAR Bottom = SELECTEDVALUE ( EnteteRapportAgentClient[Bottom] )

The same goes for all of the different calculations. You need to set the filter context that you'd expect if the header were based on your actual dimensions rather than an artificial constructed header.

wrong values with customized double header in matrix

1 Answers