How to count in the table (with conditions)

[saranp780]

I'm trying to get counting the username and the date which is related. I have a table below

Date                        Username                    Computer Name

1/10/2017               ACT001                         Manage of Accounting Dep.

1/10/2017               ACT001                         Manage of Accounting Dep.

1/10/2017               ACT001                         Director of Acc.

2/10/2017               ACT001                         Manage of Accounting Dep.

2/10/2017               ACT001                         Manage of Accounting Dep.

2/10/2017               ACT002                         ACC. Staff02

2/10/2017               ACT002                         ACC. Staff002-TH

2/10/2017               ACT002                         ACC. Staff002-TH

3/10.2017               ACT001                         Manage of Accounting Dep.

3/10/2017              ACT003                         ACC> Staff03

4/10/2017              ACT002                         ACC. Staff002-TH

4/10/2017              ACT002                         ACC. Staff002-TH

Now I want to count by condition: if the same user on the same date then just count only one time.

Such as ACT001 = 3 days (How many days user used?)

             ACT002 = 2 days

             ACT003 = 1 day

Anyone who knows? please explain me how is the formula?

Thanks.

[v-ljerr-msft · Accepted solution]

Hi @saranp780,

In addition, you should also be able to simply use the formula below to create a single measure to get the count in your scenario. 

Measure =
SUMX (
    SUMMARIZE ( Table1, Table1[Username], "abc", DISTINCTCOUNT ( Table1[Date] ) ),
    [abc]
)

Regards

[dilumd]

Hi @saranp780

First create a calculated column to combine your date column and the user name column.

Date and Text = CountRawsFile[Date     ] & CountRawsFile[Username]

Then create the below measure to calculate the no of users.

NO of Raws = CALCULATE(DISTINCTCOUNT(CountRawsFile[Date and Text]),ALLEXCEPT(CountRawsFile,CountRawsFile[Username]))