Calculate count of material by number of times showed in table

[rfinguerweg]

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?

[v-cgao-msft]

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

[rfinguerweg]

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:

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.

[gmsamborn]

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 · Accepted solution]

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

[rfinguerweg]

Thanks, it worked just fine!!