Get a count of a particular column, based on another column of the same table - Power BI

3
votes

I have two tables.
Table A and Table B.

The relationship from Table A to Table B is one to many.
One Table A record gives me several Table B records.

Table A contains my master data and Table B contains additional data.

Here is what the two tables look like:

Table A:

 _________________________________
| ... | Confirmation Number | ... |
|_____|___________________________|
| ... |       7413080       | ... |
|     |                     |     |

Table B:

 ______________________________________________________________
| Confirmation Number | Occurrences | Name  |      Status      |
|_____________________|_____________|_______|__________________|
|       7413080       |      1      | DAVE  |  Not Clocked Off |
|_____________________|_____________|_______|__________________|
|       7413080       |      2      | STEVE |       Fine       |
|_____________________|_____________|_______|__________________|
|         ...         |     ...     |  ...  |        ...       |

The data I wish to display in my Power BI report is:
1) The total amount of people (unique count on Name) - which I have achieved without issue
2) Based on the total amount of people, the number of Fine records
3) Based on the total amount of people, the number of Not Clocked On records

On my main screen, users can filter the data (Table A records) - which gives me the relevant number of records for Table B.

On Table B, for #1, I am doing a count of distinct records on the Name column: enter image description here

Which gives me the desired result (bottom left): enter image description here

To achieve #2 and #3, I created the same Card with a count of Status. enter image description here

78 is the number (count) of 'statuses' I have in Table B - that's fine I thought, I just needed to filter those results to show me how many records there are of Not Clocked Off...

So I created a New Quick Measure on the Status column, for a filter of Not Clocked Off.

enter image description here

Which gives me 29 records - that's 29 rows in Table B which have the status of Not Clocked Off. But this is meaningless to my users, I wish to have the total amount of people with the status of Not Clocked Off. Which means getting the distinct count of Name for the filter of Not Clocked Off.

How can I achieve this?

Doing a distinct count of Status gives me 2 (which is again, useless because of course there are 2 status types - Fine and Not Clocked Off).

Here is my relationship link: enter image description here

2
powerbipowerbi-desktoppowerbi-datasource

2 Answers

4
votes

You'll want to write the measure yourself (instead of a quick measure) so you can get exactly what you want.

Try this:

CountNotClockedOff =
CALCULATE (
    DISTINCTCOUNT ( TableB[Name] ),
    FILTER ( TableB, TableB[Status] = "Not Clocked Off" )
)

For CountFine just change "Not Clocked Off" to "Fine" in the above.

-3
votes

Use a relationship. Go to datasets and define relation ship. Make sure the table you group by filters the other table.

then Drop what ever you grop on in Table one then drop what u need to count in table 2. You can create a measure with distinct count rows