Forum Discussion
Count rows for multiple columns
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
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?
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
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 !