Distinctcount with COUNTROWS

[Ley]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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.