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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mclaur_and_22
Frequent Visitor

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): 

EVID2.png 

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 

 

1 ACCEPTED SOLUTION

Hi, @mclaur_and_22 

 

You can try:

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

 

vjaneygmsft_0-1643081373864.png

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
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. 

 

EVID4.png

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!! 

Hi, @mclaur_and_22 

 

You can try:

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

 

vjaneygmsft_0-1643081373864.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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