Count rows for multiple columns

[DarSz]

Hello, I have following table in pbix:

Id   |  class1  |  class2  |  ....  |  class99 

1    | status1 | status2 |  ....  |  status5

2    | status2 | status2 |  ....  |  status1

3    | status3 | status1 |  ....  |  status5

...    |     ...      |     ...      |  ....  |    ...      

999 | status2 | status5 |  ....  |  status4

In Power BI I need to complete a few tasks:

1) Is there any intelligent way to count each status by class? I would rather avoid to create measure for every status and class

2) Is there any way to count occurence of each status in the table? 

3) Furthermore, in report view I need to create matrix where I can see count of every status by class.

I need help with guguring out solution for this problems. Is this even achievable in PowerBI?
Thanks in advance!

[BA_Pete · Accepted solution]

Hi @DarSz ,

In Power Query, you'll want to unpivot your class columns.

Select your [id] column, then on the Transform tab choose Unpivot Columns dropdown and select Unpivot Other Columns.

This should get you something like this:

Then your measures only need to reference the [class] (attribute) column and the [status] (Value) column.

You could then create a simple measure as follows:

_noofValues = COUNTROWS(yourTable)

You can then use this measure in a matrix to show the intersect count of class vs status, something like this:

Pete

[DarSz]

Thanks for your reply. Unpivoting works, but it brings another problem.

My database contains around 200k records (and counting) and 30 classes (probably more in the future). With unpivoting I'm afraid about performance of my database. Is there any other workaround? Or maybe I shouldn't be worried and PowerBI can handle such blown database?

[BA_Pete]

Hi @DarSz ,

Power BI can handle millions of rows, so don't worry too much about that.

Regarding performance, AFAIK pivoting/unpivoting requires the entire table to be loaded into memory to complete, so this shouldn't affect your database any more than the original import - this step won't be folded back to source. However, in line with this, I would imagine this step to be a bit painful if you're performing it on the full dataset on your laptop. If you're planning on publishing this to the PBI Service, then your gateway should pick up this heavy lifting and, as a (presumably) dedicated resource, it should get through it without too much trouble, particularly if you're running refreshes overnight when general system/network congestion is much lower.

In my humble opinion, this is the way it should be done - no workarounds. The only other option would be to write bespoke measures on your original table structure which I would politely describe as 'unfeasible'.

Pete

[DarSz]

I performed tests on database snapshot and everything went smoothly. PBI Service is indeed responsible for 90% of my calculations. Refresh with all necessary data including unpivoted columns was done after 2 hours, which is fine by me.

Thank you for you replies @BA_Pete and @amitchandak !

[amitchandak]

@DarSz , One of the way is that you unpivot the columns into rows

https://radacad.com/pivot-and-unpivot-with-power-bi

Then you can get these answers easily