Forum Discussion
Count rows with multiple conditions
I would love to get some help in counting how many of the 'courses that count for credit' a 'person' has taken, i.e.; if you take a 'courses that counts for credit' twice you only recieve credit for one. Right now all I have is the Person/Course Table. My expected results are shown in the last table below. e.g.; Eventhough Squares is a course that counts for credit, Bill only gets one credit when he actually took Squares twice.
| Person | Course |
| Bill | Squares |
| Bill | Squares |
| Bill | Triangles |
| Bob | Circles |
| Bob | Rectangles |
| Lisa | Squares |
| Lisa | Triangles |
| Lisa | Rectangles |
| Lisa | Rectangles |
| Jennifer | Ovals |
| Tom | Polygons |
| David | Ovals |
| David | Squares |
| Daniel | Squares |
| Courses that count for credit |
| Squares |
| Rectangles |
| Ovals |
| Expected Output | |
| Bill | 1 |
| Bob | 1 |
| Lisa | 2 |
| Jennifer | 1 |
| Tom | 0 |
| David | 2 |
| Daniel | 1 |
Hello powerbiuser12
You can use something like this.
Counted Courses = VAR Countedcourses = {"Squares", "Rectangles", "Ovals"} VAR TheCount = CALCULATE ( DISTINCTCOUNT ( YourTable[Course] ), YourTable[Course] IN ( Countedcourses ) ) RETURN IF ( ISBLANK ( TheCount ), 0, TheCount )
3 Replies
Hello powerbiuser12
You can use something like this.
Counted Courses = VAR Countedcourses = {"Squares", "Rectangles", "Ovals"} VAR TheCount = CALCULATE ( DISTINCTCOUNT ( YourTable[Course] ), YourTable[Course] IN ( Countedcourses ) ) RETURN IF ( ISBLANK ( TheCount ), 0, TheCount )Thanks for the super fast response jdbuchanan71 ! It worked. Hopefully I put this issue in generic enough terms others will gain value from it too.
I was trying to think of a way to do it using IF, Countrows, Distinct, and Filter but couldn't put all the pieces together. Your solution is simple and elegant but seemingly a little more advanced to a new user like myself by creating variables. Is there a "messier" version using more off-the-shelf functions in DAX perhaps that I should have been able to piece together?Again thanks for the help.
One other solution I tried was to display the data in a Matrix with the Courses in the Rows and the Names in the Columns then apply a Visual level filter and select only the "Courses that counted for credit". The downside is I have about 500+ names and the Matrix stops at about letter C in my alphabetical names list so I applied a data slicer to let the report user hone in on a name or set of names but the solved solution does what I was after better.
