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

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

    • Dtrain's avatar
      Dtrain
      Helper I
      4 years ago

      Thank you for helping.

       

      what you had highlighted in question in the total relate to "Female" numbers.  CreatedNo & ModifiedNo has been filtered to "Male" only and I have updated the title as you can see in the snapshot. 

       

       

       

      The way I write the measures for each

       

      CreatedNo (MaleOnly) =
      VAR CreatedMaleSeleceted = CALCULATE(DISTINCTCOUNTNOBLANK('table'[CreatedId]),'table'[Gender] = "Male")

      RETURN
      CreatedMaleSeleceted
       
       -------------------------------------
      ModifiedNo (MaleOnly) =
      VAR ModifiedMaleSeleceted = CALCULATE(DISTINCTCOUNTNOBLANK('table'[ModifiedId]),'table'[Gender] = "Male")

      RETURN
      ModifiedMaleSeleceted

       

      -------------------------------------------------

      Total (Male&Female) =

      COUNTROWS(
      DISTINCT (
      UNION (
      DISTINCT( FILTER(VALUES('table'[CreatedId]), 'table'[CreatedId] <> BLANK())),
      DISTINCT( FILTER(VALUES('table'[ModifiedId]), 'table'[ModifiedId]<>BLANK()))
      )
      ))
       
      --------------------------------------
      Total Male Only =
      VAR GenderType = SELECTEDVALUE('table'[Gender])
      VAR Combine = COUNTROWS(
      DISTINCT (
      UNION (
      DISTINCT( FILTER(VALUES('table'[CreatedId]), 'table'[CreatedId] <> BLANK())),
      DISTINCT( FILTER(VALUES('table'[ModifiedId]), 'table'[ModifiedId]<>BLANK()))
      )
      ))

      RETURN

      IF(GenderType = "Male",Combine, BLANK())
       
      ---------------------------
      The aim is to get the "Total Male Only" measure to combine "CreatedNo (MaleOnly)" & "ModifiedNo (MaleOnly)"  then count the unique ID without Blank for only "Male" and show the total at the bottom.
       Sorry for making it easy to understand and hope I have explained it better this time. 
      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        Hi,  Dtrain 

         

        I need you to express the logic of the result you want in words, not the measure you wrote. 

        And I need your sample raw data (in table form not in picture).  So I can help you modify one.

        Or you can try:

        Total Male Only =
COUNTROWS (
    DISTINCT (
        UNION (
            DISTINCT (
                FILTER (
                    VALUES ( 'table'[CreatedId] ),
                    'table'[CreatedId] <> BLANK ()
                        && SELECTEDVALUE ( 'table'[Gender] = "Male" )
                )
            ),
            DISTINCT (
                FILTER (
                    VALUES ( 'table'[ModifiedId] ),
                    'table'[ModifiedId] <> BLANK ()
                        && SELECTEDVALUE ( 'table'[Gender] = "Male" )
                )
            )
        )
    )
)

         

        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