矩阵中自定义双标题的错误值 [英] wrong values with customized double header in matrix

问题描述

我为标题使用自定义计算表，这是我之前问题的答案:

以下是预期结果:

解决方案

这里的关键是您需要将标头上下文作为过滤器上下文传递给您的度量.

例如，而不是行

Top1 = "Affaires nouvelles" &&底部索引 <>0, [AN]+0,

您需要将 [AN] 替换为

计算([AN]，Dim_Produit[Dim5Rapport] = 中间，Dim_Produit[Dim6Rapport] = 底部)

在哪里

VAR Middle = SELECTEDVALUE (EnteteRapportAgentClient[Middle])VAR 底部 = SELECTEDVALUE (EnteteRapportAgentClient[底部])

所有不同的计算也是如此.如果标题基于您的实际尺寸而不是人工构造的标题，您需要设置您期望的过滤器上下文.

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:

解决方案

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.

这篇关于矩阵中自定义双标题的错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！