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 measure to include this?

 

Act ID =

COUNTROWS (

DISTINCT (

UNION (

DISTINCT (FILTER(VALUES('table'[CreatedNo]), 'table'[CreatedNo] <> BLANK() )),

DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),'table'[ModifiedCreatedNo]<>BLANK()) )

)))

Need Help
  • 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

14 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Dtrain , Try one of the two

     

    Act ID =

    COUNTROWS (
    DISTINCT (
    UNION (
    DISTINCT (FILTER(VALUES('table'[CreatedNo]), not(isblank('table'[CreatedNo]) )),
    DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),not(isblank('table'[ModifiedCreatedNo]))) )
    )))

     

     

    or


    Act ID =

    COUNTROWS (FILTER(
    DISTINCT (
    UNION (
    DISTINCT (FILTER(VALUES('table'[CreatedNo]), not(isblank('table'[CreatedNo]) )),
    DISTINCT ( FILTER(VALUES('table'[ModifiedCreatedNo]),not(isblank('table'[ModifiedCreatedNo]))) )
    )) , not(isblank('table'[CreatedNo]) ) )))

    • Dtrain's avatar
      Dtrain
      Helper I
      4 years ago

      Sorry, I dont think I explained it correctly. I also have a "gender" colomn that I would like to include. Using your measure I can get the Total which is what I need but I also need to get the results out of the total to split out the "Males" number and also show the total at the bottom. 

       

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        Hi, Dtrain 

         

        Can you explain the logic of ''total' ?I can't understand some results according to your needs.

        If you can upload some insensitive data samples and expected output, I can make a workaround for you.

         

        Best Regards,

        Community Support Team _ Janey