Forum Discussion
Help with DAX for double group count and sum
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
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
3 Replies
- amitchandak
Super User
mclaur_and_22 , Create two measures like
cnt = COUNTROWS(Table)
countx(filter(Values(Table[Enlace]), [cnt] >1), [Enlace])
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
Community Support
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
