COUNTA of occurences in Column A with distinct of Column C

[PuddleRunna]

Hi again,

I have a pretty simple measure that counts the number of software assignements on an asset (

SW Package count = COUNTA(Softwarezuweisung[Hostname])). 

Now I have occurences where the same asset has 2 or more users assigned. If I filter my visuals on User 1, it'll return a count of e.g. 4 although the user has only 2 packages (My visuals also display the packages itself, so the "discrepancy" is noticable).

Table "Softwarezuweisung":

Hostname	SW Package	User
123456	Package 1	Jane Doe
123456	Package 1	John Doe
123456	Package 2	Jane Doe
123456	Package 2	John Doe

 Also, if I filter for both users, both come back with a count of 4:

User	SW Package Count
Jane Doe	4
John Doe	4

How do I adjust the measure to count the occurences per asset and per user? 

Something with Calculate, Counta and Filter? Not sure how to even approach this 🙂

[ryan_mayu · Accepted solution]

@PuddleRunna 

maybe you can try this

Measure 2 = CALCULATE(COUNTROWS('Table (2)'),FILTER(all('Table (2)'),'Table (2)'[Hostname]=MAX('Table (2)'[Hostname])))

[Hariharan_R]

Hi

By defult it shows 2 only as a result. Please share the expected result for more clarity.

Thanks

Hari

[PuddleRunna]

Hi @Hariharan_R , @ryan_mayu 

Got it to work now by tweaking the FILTER Section:

Calculate Measure = CALCULATE(COUNTA(Softwarezuweisung[Hostname]),FILTER(all(Softwarezuweisung),Softwarezuweisung[User]=MAX(Softwarezuweisung[User])))

Thanks a lot for your help on this. Didn't even know where to start 🙂

[PuddleRunna]

Hi @ryan_mayu ,

this still returns 4 per user when I would expect a total of 2 for each and an overall total of 4.

[ryan_mayu · Accepted solution]

@PuddleRunna 

like what @Hariharan_R said, COUNTA should return 2 for each user and 4 for total. please provide the expected output

[ryan_mayu · Accepted solution]

@PuddleRunna 

maybe you can try this

Measure 2 = CALCULATE(COUNTROWS('Table (2)'),FILTER(all('Table (2)'),'Table (2)'[Hostname]=MAX('Table (2)'[Hostname])))