Forum Discussion

MPetramalo214's avatar
MPetramalo214
Helper I
4 years ago
Solved

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. ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
MPetramalo214
Helper I
4 years ago

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.