Distinct count with filter and total

[Dtrain]

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()) )

)))

[amitchandak]

@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]

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]

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]

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]

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

[Dtrain]

Sorry I'm still new to this. I have change the data sample to better explain.

Here is the dataset to explain what I'm trying to do.

Auction ID   CreatedByCusId   AcceptedByCusId    Auction Type      Amount            Date
10656           1133                    1144                         In-Store             1                       4/06/2021
10665           1143                    1107                         In-Store             1                       4/06/2021
10666           1125                                                     In-Store             1                       4/06/2021
10674           1133                    1117                         In-Store             1                       4/06/2021
10691           1115                    1066                         In-Store             1                       7/06/2021
10692           1133                                                     Online               5                       7/06/2021
10693           1144                                                     Online               5                       7/06/2021
10694           1144                                                     In-Store             1                       7/06/2021
10695           1133                                                     Online               5                       7/06/2021
10696           1133                   1066                          Online              5                        7/06/2021
10697           1133                   1125                          Online              5                        7/06/2021
10698           1144                                                     Online             50                       7/06/2021
10699           1116                  1122                           In-Store            1                        7/06/2021
10700           1066                  1125                           In-Store            1                        8/06/2021
10701           1159                  1121                           In-Store            1                        8/06/2021
10702           1121                                                     In-Store            1                        8/06/2021
10703           1095                 1066                            In-Store            1                       14/06/2021
10704           1121                                                    Online               5                       14/06/2021
10705           1121                  1066                          In-Store             1                       14/06/2021
10706           1159                  1121                           In-Store            1                       14/06/2021
10707           1121                   1117                          In-Store            1                       14/06/2021
10708           1159                  1144                          In-Store             1                       14/06/2021
10709           1144                                                   In-Store              1                       15/06/2021
10710           1115                                                   Online                5                       15/06/2021
10711           1121                  1117                         In-Store              1                       15/06/2021
11296          1107                   1175                        Online                 5                       31/07/2021
11297          1175                   1107                        Online                 5                       28/07/2021
11300          1095                                                   Online                 5                       30/07/2021
11319          1119                   1114                         Online                5                        6/08/2021
11340          1117                                                    Online                5                        20/08/2021
11341          1123                                                    Online               5                        20/08/2021
11351          1121                1260                            Online               5                        5/08/2021

I would like to see by Dates in:

1st column - how many unique Customer created a bid "In-Store"

2nd column - how many unique Customer accepted a bid "In-Store"

3rd column- how many unique Customer both created & accepted a bid "In-Store"

4th column- Total unique Customer both created & accepted a bid & both In-Store & Online.

I need to show the total result for each column at the bottom.

I hope this is easier to understand.

Thanks for making the time to help.

[v-janeyg-msft]

Hi, @Dtrain 

Sorry I just saw your addition, I basically understand your needs, if you can upload sample data in table format for me to test, I can solve it faster.

I will get back to you tomorrow.

Best Regards,

Community Support Team _ Janey

[Dtrain]

I keep on getting this error when creating a table. What am I doing wrong?

[v-janeyg-msft]

[Dtrain]

Still giving me the error when trying to inserting the table. any other suggestion? 

[v-janeyg-msft]

@Dtrain 

Can you share an simple excel link or sample file link?

[Dtrain]

https://docs.google.com/spreadsheets/d/1FmdUbCdCAoUhymUDQTnug2g--knCWB5e/edit?usp=sharing&ouid=11283...

[v-janeyg-msft · Accepted solution]

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

[Dtrain]

Thanks you so much for your support.