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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors