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
MPetramalo214
Helper I
Helper I

Using two Distinct Count of fields in a matrix

I have a weird case that I've been racking my brain on and I just can't seem to figure this out.  Any help would be appreciated because I'm sure this is an easy solve that I'm just not thinking of.

 

We're working with a single table, with three columns of importance.  Owner ID, Animal ID, and Species ID. 

 

What I need to solve is:

What is the DISTINCTCOUNT of Owner ID's

WHERE the DISTINCTCOUNT of Animals ID's with matching species ID's.

 

Essentially the count of owners, who have each number of dogs, cats, etc.

 

I'd like to see something along the lines of:

Count of Owner IDsDog (Species ID 1)Cat (Species ID 2)
36710
18201
45611
59220
41221

 

Essentially this would tell me, 367 have 1 dog, 0 cats.  182 owners have 0 dogs 1 cat.  456 owners have 1 of each.  592 owners have 2 dogs. and so on. 

 

Right now, each owner id is being listed out separately in a matrix, and I can't get a count of them.  With over 300,000 owners, its impossible to really get anything meaningful out of it.  And exporting to a csv file cuts off at 30,000 (10%).

 

Hope this makes sense, can't seem to google the right keywords to get anything meaningful.

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @MPetramalo214 

In this scenario, its essence is to count according to the combination of cat and dog. However, the matrix cannot combine people with different ids, so you need to create a measure. In matrix, measure cannot be put in Rows (see picture below). So it needs to create a dimension table first.

vxiaotang_0-1647933417011.png

(1) create a dimension table

vxiaotang_1-1647933663265.png

(2) create a measure

Count of Owner IDs =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[owner_id] ),
    FILTER (
        ALL ( 'Table' ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[owner_id] ), 'Table'[species_id] = 1 )
        )
            = MIN ( 'dimension table'[Dog] )
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[owner_id] ), 'Table'[species_id] = 2 )
            )
                = MIN ( 'dimension table'[Cat] )
    )
)

result

vxiaotang_2-1647933711346.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@MPetramalo214 I totally missed it, I was supposed to work on this. Anyhow, you have a great solution but I feel that solution is only good if you have two species, if you have more than 2 species (maybe it never happens) this solution is not going to work. Anyhow, I guess you are in a good place, sometime in the near future I will look into a more scalable solution which will be able to address any change in the data.

 

Cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Cheers! Good to know the limitations of this solution.  I will work for the foreseeable future, but I'm always interested in learning more scaleable options I can apply to future use cases.  Thanks for the input!

v-xiaotang
Community Support
Community Support

Hi @MPetramalo214 

In this scenario, its essence is to count according to the combination of cat and dog. However, the matrix cannot combine people with different ids, so you need to create a measure. In matrix, measure cannot be put in Rows (see picture below). So it needs to create a dimension table first.

vxiaotang_0-1647933417011.png

(1) create a dimension table

vxiaotang_1-1647933663265.png

(2) create a measure

Count of Owner IDs =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[owner_id] ),
    FILTER (
        ALL ( 'Table' ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[owner_id] ), 'Table'[species_id] = 1 )
        )
            = MIN ( 'dimension table'[Dog] )
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( ALLEXCEPT ( 'Table', 'Table'[owner_id] ), 'Table'[species_id] = 2 )
            )
                = MIN ( 'dimension table'[Cat] )
    )
)

result

vxiaotang_2-1647933711346.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

This worked perfectly.  Thank you!

parry2k
Super User
Super User

@MPetramalo214 this is a really interesting question, just wondering if this is what your raw data looks like:

 

if not, can you paste sample raw data in a table format?

 

parry2k_1-1647548444229.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I've got this filtered down to just my owner id, as well as dropped about 20 columns that aren't important, rarely have info etc. The three columns I really need are:
id which is the animal identifier

owner_id which is the owner identifier

and species_id which tells me if its a dog or a cat. (1 = dog, 2 = cat)

 

MPetramalo214_0-1647548831629.png

Edit: Tagging @parry2k 

amitchandak
Super User
Super User

@MPetramalo214 , Try a measure like this example 

countx(filter(Summarize(Table[Owner ID], "_1", calculate(DISTINCTCOUNT(Table[Animal ID]) , Filter(Table, Table[Species ID] =1)), "_1", calculate(DISTINCTCOUNT(Table[Animal ID]) , Filter(Table, Table[Species ID] =2))), [_1] =[_2]), [Owner ID])

@amitchandak 

Test Measure = 
COUNTX (
    FILTER (
        SUMMARIZE (
            Animals,
            "_1",
                CALCULATE (
                    DISTINCTCOUNT ( Animals[id] ),
                    FILTER (
                        Animals,
                        Animals[species_id] = 1
                    )
                ),
            "_2",
                CALCULATE (
                    DISTINCTCOUNT ( Animals[id] ),
                    FILTER (
                        Animals,
                        Animals[species_id] = 2
                    )
                )
        ),
        [_1] = [_2]
    ),
    Animals
)

Here is the code I have in my measure currently. The first parameter for summarize is showing as a required table, so I just put the table name in there.  Same thing with the final parameter, it won't let me pass a column in.  Only an expression or a table.  I've tried both DistinctCount(Animals[Owner_ID]), as well as the table itself.

 

I may not be putting the right visualization, but when I try to put it in a matrix or table, it returns blank.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.