Forum Discussion

Dtrain's avatar
Dtrain
Helper I
4 years ago
Solved

Distinct count with filter and total

Hi,   I am use the below to distinct count two column (excluding blank) and trying to add an extra filter from a third column and have the total. Can someone help with the best way to write the mea...
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Dtrain 

     

    I have been busy recently, sorry to reply you late. You need to use sumx() and summarize() function to calculate toal.

    Like this:

    Total In-store =
    SUMX (
        SUMMARIZE (
            'Table',
            [Date],
            "a",
                COUNTROWS (
                    (
                        DISTINCT (
                            CALCULATETABLE (
                                UNION (
                                    CALCULATETABLE (
                                        DISTINCT ( 'Table'[AcceptedByCusId] ),
                                        FILTER ( 'Table', 'Table'[AcceptedByCusId] <> BLANK () )
                                    ),
                                    DISTINCT ( 'Table'[CreatedByCusId] )
                                ),
                                'Table'[Auction Type] = "In-Store"
                            )
                        )
                    )
                )
        ),
        [a]
    )
    

    Other measures are similar, you can refer to my sample below.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey