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

问题描述

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

以下是预期结果：

解决方案

此处的关键是您需要将标头上下文作为过滤器上下文传递给度量。

例如，代替一行

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

您需要替换该 [AN ] 与

 计算（[AN]，Dim_Produit [Dim5Rapport] =中间，Dim_Produit [Dim6Rapport] =底部）
  

其中

  VAR中间= SELECTEDVALUE（EnteteRapportAgentClient [中间]）
 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屋！