Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Distinct count two different columns by group

Hello,

I'm trying to count how many times a certain string value is repeated across a group of IDs. By using DISTINCTCOUNT this works perfectly when only counting a column, but I can't get around to count two different columns two create a visual.

Here's a sample of my data.

 

IDW1W2
1111YesYes
1111YesYes
1112NoYes
1112NoYes
1113YesYes
1113YesYes

 

The end result should be a Matrix like this:

 

Y/NW1W2
Yes23
No10

 

However, since I'm using the "W1" field in Rows of the matrix, I keep getting this result:

 

Y/NW1W2
Yes22
No11

 

Any help would be appreciated!

Thanks in advance.

1 ACCEPTED SOLUTION
robadob
Frequent Visitor

Apologies, this is above my paygrade... 

 

Maybe you could duplicate the table in query edit, strip out everything that isn't needed for this, unpivot it, link it back on ID:

 

robadob_0-1644952767422.png

 

robadob_1-1644952799204.png

If that doesn't I don't think I can solve it 🙂

 

View solution in original post

4 REPLIES 4
robadob
Frequent Visitor

Apologies, this is above my paygrade... 

 

Maybe you could duplicate the table in query edit, strip out everything that isn't needed for this, unpivot it, link it back on ID:

 

robadob_0-1644952767422.png

 

robadob_1-1644952799204.png

If that doesn't I don't think I can solve it 🙂

 

Anonymous
Not applicable

Yes, this works! Thank you.

Another way I got around to do it was duplicating the tables (One containing ID and W1, the other ID and W2) and creating a relationship with a Table with a Field that contains "Yes" and "No", this field goes in the rows of the desired visual. That way I didn't need to unpivot.

robadob
Frequent Visitor

I might have misunderstood but try this:

  1. edit the query for your table,
  2. select id column
  3. unpivot other collumns

robadob_0-1644948016207.png

so it looks like: 

robadob_1-1644948052862.png

Then save the query and make your matrix:

robadob_3-1644948133035.png

Result:

robadob_2-1644948111563.png

Hope the helps! 🙂 

 

 

Anonymous
Not applicable

Hi! Thanks for the help.

Is there a way other than by unpivoting columns? Thing is, this is a great simplification of my real data (one that already has been unpivoted). I want to avoid doing it again.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.