Using two Distinct Count of fields in a matrix

[MPetramalo214]

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 IDs	Dog (Species ID 1)	Cat (Species ID 2)
367	1	0
182	0	1
456	1	1
592	2	0
412	2	1

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.

[parry2k]

@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!!

[MPetramalo214]

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 · Accepted solution]

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.

(1) create a dimension table

(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

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.

[MPetramalo214]

This worked perfectly.  Thank you!

[parry2k]

@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?

[MPetramalo214]

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)

Edit: Tagging @parry2k 

[amitchandak]

@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])

[MPetramalo214]

@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.