counting the rows in a table visual respecting slicers

[saviola07]

Hi there,

I have tried searching for the answer in the archive for the following issue I have but didnt find anything that really could fix it so here goes:

My underlying data set consists of some million lines of sales data. To make it digestable for the end-users, I have made some reports with visuals etc. But I have also created a report that serves the need of them creating a table with the data they need so they can export it to Excel to conduct their own analyses. As there is a limit of 150k lines that can be exported to Excel, I need a "Card"-visual to show how many rows there are in the table they have created.

In this table, I haven't added all the columns that is part of the underlying data (this is a way to limit the amount of rows).

The idea is that by using around 10-15 slicers the end-user will be able to get the data they need and then export it.

To exemplify what I have done:

Here is how the underlying data could look like:

Month	Date	Client	Product Category	Product Type	Quantity
1	01-01-2020	A	BB	CCC	1
1	02-01-2020	A	BB	CCC	1
2	01-02-2020	A	BB	DDD	1

Here is how my table in report looks like:

Month	Client	Product Category	Product Type	Quantity
1	A	BB	CCC	2
2	A	BB	DDD	1

If I use a count-measure, I will get a count of 3 in my report table as it will look at the underlying data set and not the new table.

So my question is how do I set up a row counter that takes into consideration the selection of slicer values that might be selected by the end-user and actually show the amount of rows in this table visual and not how many rows it would be if the table was formatted in the same way as the underlying data table?

(I am unable to share the data set as it is confidential stuff).

I hope the above makes sense and someone can help (I am a rookie in Powerbi!) 🙂

Best regards,

Dan

[rainer1]

Hi,

wich column do you want to count ?

Your report table looks like this because you leaf out the Date column then power bi wrap it together.

If you want to Count the distinct value of the Product type go to the Fields tab under Visualizations and set the Count to Count(Distinct).Then only the unique Value will counted.

  

-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

[Mariusz · Accepted solution]

Hi @saviola07 

You can create a Measure like below.

Measure = 
COUNTROWS(
    GROUPBY(
        'Table',
        'Table'[Month],
        'Table'[Client],
        'Table'[Product Category],
        'Table'[Product Type]
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[saviola07]

Hi Mariusz,

That almost works as a charm!

The table-visual is using parameters from different tables. For some reason, I am unable to add parameters from a specific table as a "group-by"-variable in the count-measure. This means that the "card-"visual is slightly underestimating the actual count. Do you have an idea of how to fix this?

BR,

Dan

[Mariusz]

Hi @saviola07 

if you list your fact table ( one with Many sides of the relationship ) as a first argument it should pick up all the filters coming from any tables on one side of the relationship.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn