Help with DAX for double group count and sum

[mclaur_and_22]

Hi eveybody, 

I'm recently new to PowerBI and its calculated measures and I'm currently having problems with a DAX that I want to create: I have a table with several columns, but the ones I wanted to group were a reference ID (the Id of costumer) called REFERENCIA, the Id of a product (called ENLACE) and the code for a failure ticket for each product (called TICKET). So a costumer can have several products (and the code of the product is not unique, so several costumers can have the same product) and a product can have several failure tickets. 

The data can be seen as follows (like there is only one product that have more than 1 ticket then I would take this only product for the total calculation): 

 

So I would expect something like (based on the previous table): 

Total_count_products=1 

What I would like to have is a DAX measure where I can count the number of Id products (based on its reference) that have more than 1 failure ticket generated (like to know which products have recurrent failures). So far, I have able to count the number of unique products for each customer, however I don´t know how to do this taking into account the number of ticket per products. 

The measure I was able to get is: 

Medida = CALCULATE(COUNTROWS(Table),DISTINCT(Table[Enlace))

I have shared a pbix file where you can see more info: pbix file 

Thanks in advance 

[amitchandak]

@mclaur_and_22 , Create two measures like

cnt = COUNTROWS(Table)

countx(filter(Values(Table[Enlace]), [cnt] >1), [Enlace])

[mclaur_and_22]

Hi @amitchandak, thank you for replying, I have use your formulas and what I get is the total count of costumers that have more than 1 product, instead of the total acount of products (per costumer) that have more than 1 ticket. 

As you can see, the measure give me a total sum of 3, which would the number of references that have more than 1 product, according to the table the total sum should be two, because two products of all the products listed presented more than 1 ticket. 

Thank you!! 

[v-janeyg-msft · Accepted solution]

Hi, @mclaur_and_22 

You can try:

Count = COUNTROWS(filter(SUMMARIZE(Inf_fallas,[REF],[ENLACE],"count",COUNT(Inf_fallas[ENLACE])),[count]>1))

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey