cancel
Showing results 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

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:

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
Super User

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

5 REPLIES 5
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

Frequent Visitor

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

It turns out from this:

to this:

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.

Super User

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

Super User

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

Frequent Visitor

Thanks, it worked just fine!!

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors