Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ley
Helper I
Helper I

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
3 ACCEPTED SOLUTIONS
v-frfei-msft
Community Support
Community Support

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

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

View solution in original post

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.