Forum Discussion

Ley's avatar
Ley
Helper I
6 years ago
Solved

Distinctcount with COUNTROWS

Hi,

I want to create a measure to calculate the number of IDs (DISTINCTCOUNT(ID) which meet some condition and the number of transitions of those ID are greater than 3.

Conditions are:

Historico[Anomalia3] = "Anomalia3"&&Historico[EmpresaSopInicial] ="ViewNext"&&Historico[EstadoInicial] <> "Resolved"&&Historico[EmpresaSopFinal] ="VN"&&Historico[EstadoFinal] = "Assigned")

 

I have tried with the following measure: 

Caso por número =
count(
CALCULATE (
DISTINCTCOUNT( ( Historico[ID de incidencia]));
   FILTER(Historico[Anomalia3] = "Anomalia3"&&Historico[EmpresaSopInicial] ="ViewNext"&&Historico[EstadoInicial] <> "Resolved"&&Historico[EmpresaSopFinal] ="VN"&&Historico[EstadoFinal] = "Assigned")
) > 3)
 
But this does not work,
Thanks in advance
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Ley ,

     

    Please update your formula like that.

    Measure = 
VAR dis =
    CALCULATE (
        DISTINCTCOUNT ( Historico[ID de incidencia] ),
        FILTER (
            Historico,
            Historico[Anomalia3] = "Anomalia3"
                && Historico[EmpresaSopInicial] = "ViewNext"
        )
    )
RETURN
    CALCULATE ( COUNTROWS ( Historico ), FILTER ( Historico, dis > 3 ) )

    If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

  • Ley's avatar
    Ley
    6 years ago

    Hi,

    I have tryed your formula and it does not work. I want a distinctcount of ID when the number of rows associated with this ID that meet the conditions are superior to 3. I have tried the following modification of your formula, but I am not sure if it is right.

     

    _Caso por numero =
    VAR dis =
    CALCULATE (
    COUNTROWS( Historico );
    FILTER (
    Historico;
    Historico[Anomalia3] = "Anomalia3"
    && Historico[EmpresaSopInicial] = "VN"&& Historico[EstadoFinal]="Assigned"&&Historico[EmpresaSopFinal]="VN"
    )
    )
    RETURN
    CALCULATE ( DISTINCTCOUNT( Historico[ID de incidencia]); FILTER ( Historico; dis > 3 ) )

     

    Thanks in advance

  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Ley ,

     

    Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Ley ,

     

    Please update your formula like that.

    Measure = 
VAR dis =
    CALCULATE (
        DISTINCTCOUNT ( Historico[ID de incidencia] ),
        FILTER (
            Historico,
            Historico[Anomalia3] = "Anomalia3"
                && Historico[EmpresaSopInicial] = "ViewNext"
        )
    )
RETURN
    CALCULATE ( COUNTROWS ( Historico ), FILTER ( Historico, dis > 3 ) )

    If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    • Ley's avatar
      Ley
      Helper I
      6 years ago

      Hi,

      I have tryed your formula and it does not work. I want a distinctcount of ID when the number of rows associated with this ID that meet the conditions are superior to 3. I have tried the following modification of your formula, but I am not sure if it is right.

       

      _Caso por numero =
      VAR dis =
      CALCULATE (
      COUNTROWS( Historico );
      FILTER (
      Historico;
      Historico[Anomalia3] = "Anomalia3"
      && Historico[EmpresaSopInicial] = "VN"&& Historico[EstadoFinal]="Assigned"&&Historico[EmpresaSopFinal]="VN"
      )
      )
      RETURN
      CALCULATE ( DISTINCTCOUNT( Historico[ID de incidencia]); FILTER ( Historico; dis > 3 ) )

       

      Thanks in advance

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi Ley ,

         

        Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.