Forum Discussion
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 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.
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.
8 Replies
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.
This worked perfectly. Thank you!
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])
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.
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?
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 identifierowner_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
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!!
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!
