Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Proud to give back to the community!
Thank You!
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
Proud to give back to the community!
Thank You!
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?
When you 'Get Data' select 'Edit' during the import process, or after import select 'Edit Queries' from the Home Tab of the Desktop Client.
Proud to give back to the community!
Thank You!
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.
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
Proud to give back to the community!
Thank You!
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