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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rfinguerweg
Frequent Visitor

Calculate count of material by number of times showed in table

Hi there,

 

I have created a table where I can see how many materials have appeared in my table N number of times.

For example:

 

rfinguerweg_0-1696263306928.png

 

In this table, I have 1 Material that showed up 13 times in the table.

5 Materials that showed up 10 times in the table (number of rows with the same material number)

937 Materials that showed up only 1 time in the table (only 1 row with the same Material Number)

 

I created this table using a table measure:

qty count = SUMMARIZE('Materiais Comprados Faltantes (Novo)', 
'Materiais Comprados Faltantes (Novo)'[Material],
 "Count", COUNTX('Materiais Comprados Faltantes (Novo)', 'Materiais Comprados Faltantes (Novo)'[Material]))

 

I need this measure to be dynamic with date filter.

Whenever I filter date, I want to know in the period the count of materials that showed up N number of times.

 

Any Help?

1 ACCEPTED SOLUTION
gmsamborn
Super User
Super User

Hi @rfinguerweg 

 

Your requirements seem pretty similar to a solution I came up with for another user a couple of days ago.

Take a look.  Even if it doesn't really fit your requirements, you might find it useful.

 

Count duplicates.pbix

 

View solution in original post

5 REPLIES 5
v-cgao-msft
Community Support
Community Support

Hi @rfinguerweg ,

Try adding the date field in your function.

qty count =
SUMMARIZE (
    'Materiais Comprados Faltantes (Novo)',
    'Materiais Comprados Faltantes (Novo)'[Material],
    'Materiais Comprados Faltantes (Novo)'[Date],  // Add your date field here
    "Count",
        COUNTX (
            'Materiais Comprados Faltantes (Novo)',
            'Materiais Comprados Faltantes (Novo)'[Material]
        )
)

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

I had already tried that one.

 

What happens is that since it is a table measure (not only a measure), this is what happens:

 

It turns out from this:

rfinguerweg_0-1696440215294.png

 

 

to this:

rfinguerweg_1-1696440245876.png

 

 

I think the best way if I could do this using measure, not a table measure, but I don't know how to use the "Count" measure as a x-axis in the chart.

Hi @rfinguerweg 

You said "but I don't know how to use the "Count" measure as a x-axis in the chart."

If you look at my example, that's what I'm doing with the "Count" table that is used on the x-axis.  The "Count" table has no other use.

Please take another look at my example and let me know if you have any questions.

 

Count duplicates.pbix

 

 

gmsamborn
Super User
Super User

Hi @rfinguerweg 

 

Your requirements seem pretty similar to a solution I came up with for another user a couple of days ago.

Take a look.  Even if it doesn't really fit your requirements, you might find it useful.

 

Count duplicates.pbix

 

Thanks, it worked just fine!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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