Add multiple COUNTROWS in one expression  [Solved]

Anything related to PowerPivot and DAX Formuale
BINoob
Posts: 5
Joined: Tue May 14, 2019 6:01 am

Add multiple COUNTROWS in one expression

Postby BINoob » Tue May 14, 2019 6:15 am


PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Add multiple COUNTROWS in one expression

Postby PhilC » Tue May 14, 2019 12:32 pm

Hi BINoob,

Are you able to use Power Query to transform the data? ie Unpivot the 12 columns, which I assume are months of the year?

https://community.powerbi.com/t5/Deskto ... d-p/140881

If not, in the same link there is a formula you could use as a calculated column below the suggested answer, which could then be summed in a measure.

Perhaps posting a mock dataset could help with the suggested solution / approach.

Cheers
Phil

BINoob
Posts: 5
Joined: Tue May 14, 2019 6:01 am

Re: Add multiple COUNTROWS in one expression

Postby BINoob » Wed May 15, 2019 8:29 am

Hi Phil,

Forgive me if I take the Noob of the year award here.

I can use Power Query to transform the data, but not sure how to do that effectively. I have 12 columns and several possibilities per column to account for and everything I can think of seems like 24 measure to create. Here is a sample of data (comma delimited):

User,Sat1,Sat2,Sat3,Sat4
User1,5,5,5,null
User2,4,3,1,null
User3,2,1,2,2

The Sat1-Sat4 (Satisfaction rating) fields would go to 12. Each could be 1-5 or null if nothing were entered. What I need is to be able to find the percentage of entries that are 3 or less out of the total number of numeric entries. In Excel, I could do:
=SUM(
COUNTIFS(Sat1,"<4") +
COUNTIFS(Sat2,"<4") +
COUNTIFS(Sat3,"<4"))
/
(COUNTIFS(Sat1,">3") +
COUNTIFS(Sat2,">3") +
COUNTIFS(Sat3,">3"))

I'm looking to see if I can create a measure that does something similar.

Thanks,

John

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Add multiple COUNTROWS in one expression

Postby PhilC » Wed May 15, 2019 8:58 am

Hi BINoob,

Certainly nothing Noobish about asking how to achieve something. Like Excel, with Power Query / Power Pivot, there are usually multiple ways to achieve something.

So, it sounds like manipulating the data prior to loading into Power PIvot is an option. Much of what you read from people like Matt Allington, Avi Singh, The Italians is about preparing the data first and having the data model in the best shape to do the analysis you need to do.

Also, posting a file with real data is very useful (or mocked up is the data is sensitiave, which it usually is). This does take time, but really helps others looking to help as it provides something to work with.

Attached is a file with a suggested approach.
1) Using Power Query, Unpivot the Sat# columns into an Attribute and Value column (you can change the headings as necessary (ie Satisfaction Question and Question response). Might need to decide how to treat nulls, should they form part of the counts or not?
2) Load into the Data Model
3) Write basic measures and then build on those
3 - 1) Initial COUNTROWS measure is the basis of any other analysis
3 - 2) Measure to identify all responses <=3
3 - 3) Create the percentage measure required, using DIVIDE (you can use straight [measure1] / [measure2] but using DIVIDE accommodates error results).

It might become obvious that you can end up with a lot of measures, this is normal. Check out Enterprise DNA measure groups and measure branching (google this and have a look a the videos on this).

It your solution needs further development, create a good, representative dataset, try the steps in the attached file, and post new questions.

Cheers
Phil
Cheers
Phil
Attachments
Satisfaction PPF.xlsx
(135.4 KiB) Downloaded 26 times

BINoob
Posts: 5
Joined: Tue May 14, 2019 6:01 am

Re: Add multiple COUNTROWS in one expression

Postby BINoob » Wed May 15, 2019 9:09 am

Phil,

Ahhh...the dust is burning off of the gears. That puts the information in a much better perspective with regards to how to prep and manage the data. This makes those columns much more manageable. The nulls are not counted, but having everything in one column give options to get around those pretty easily. I'll mess around with this tomorrow. The information is falling into place I think.

John

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Add multiple COUNTROWS in one expression  [Solved]

Postby PhilC » Wed May 15, 2019 9:24 am

Hi John,

Great, let us know how you go.

Regarding the nulls, if the cell is actually null, it drops off the rows when you Unpivot, so they will not be in the table anymore. If you need them for the counts, you would need to replace with a 0 or other value prior to Unpivoting.

You can do this and still exclude them from the measures, if having complete data is desirable.

Good luck.

Cheers
Phil

BINoob
Posts: 5
Joined: Tue May 14, 2019 6:01 am

Re: Add multiple COUNTROWS in one expression

Postby BINoob » Thu May 16, 2019 4:18 am

Hey Phil,

Thank you very much! The unpivoted columns allowed me to use a single Measure to divide two calculated countrows statements to get the percentage. This was an elegant and simple solution compared to what I thought I was facing.

Who da man? You da man!

John


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests