Distinct values based on multiple columns

[kattlees]

I need to find distinct values in a table based on 3 columns.

Example of data would be:

PATNUM                SCHDT               SCHTM

123456                    71317                 08:00:00

123456                    71317                 08:00:00

654321                    71217                 08:30:00

654321                    71217                 19:15:00

987654                    71117                 11:15:00

987654                    71217                 05:00:00

123456 should return a count of 1, but 654321 & 987654 would both return 2.

I am very new to power bi any help would be appreciated.

[fhill · Accepted solution]

If it helps, it SUPER easy to do what you are asking in reverse...  You can select all 3 columns, then click 'Group By' and create a new Count Rows column.  Now the count will be 2 for 123456 and 1 for everyone else?  (Meaning that grouping of 3 data columns had 2 occurences, where the rest only had 1 distinct occurance of hte 3 columns.)

Hope this helps a little...

FOrrest

[kattlees]

Sorry for the newbie question, but how do I get to the screen you are showing. I have gone to modeling where I would usually create a new table, but I can't select more than one column at a time.  Are there videos or tutorials somewhere?

[fhill]

When you 'Get Data' select 'Edit' during the import process, or after import select 'Edit Queries' from the Home Tab of the Desktop Client.

[kattlees]

I was able to do the group by and it counted right, but then none of my graphs worked. I will have to play with it some more.

[fhill]

Glad you are almost there!  Remember your graphs will treat the Grouped Data as if it was the original data source, so take into account the Count column when looking for multiple enteires that are no longer there.  Thank you, FOrrest

[kattlees]

So i have a matrix showing:

Name       Type 1    Type 2   Type 3  Total

John           1                                        1

Sue                              2               2     4

Bill               1                                       1

Where it counts the records with distinct (PATNUM,SCHDT,SCHTM)  See Sue's PATNUM of 543210 has different SCHDT & SCHTM so it is counted as 2 above but Bill's have the same so it's counted as 1. Obviously there are a lot more columns. But when I do the group and get the count, I can't pull the info into the matrix anymore. Do i need to have a whole different tabel for it?

Data might look like this:

Name     PATNUM    SCHDT    SCHTM   Type 

John        123456         71117    08:00       1
Sue          654321         71117    09:00       2

Sue          987654         71117    10:00       2

Sue          543210         71117    11:15      3

Sue          543210        71217    19:45       3 

Bill           876543         71117    12:15       1

Bill           876543         71117    12:15       1