DAX Formula for COUNT (Resources)

[cristianml]

Hi,

I need to Count The quantity of resources from a query. The thing is that it has duplicated lines due to diferent reasons, Ctegory, Unit, Location, ETC.  Follow a example of what info has the table for reference..  I tried with the following formula but in same cases duplicates the quantity as it has various "Units" .. 

COUNTX(FILTER('Resource Actual','Resource Actual'[Category]="Hours" && 'Resource Actual'[Quantity]>8),'Resource Actual'[Quantity])

In this case I need that the formula COUNTS 1 for "Carlos" . 

Location	Currency:	Name	Unit	Level	Category	Quantity
Argentina	United States Dollar	Carlos	Local	Analyst	Hours	40.00
Argentina	United States Dollar	Carlos	Local	Analyst	Payroll	16,000.00
Argentina	United States Dollar	Carlos	Local	Analyst	Cost rate	400.00
Argentina	United States Dollar	Carlos	US	Analyst	Hours	21.00
Argentina	United States Dollar	Carlos	US	Analyst	Payroll	8,400.00
Argentina	United States Dollar	Carlos	US	Analyst	Cost rate	400.00
Argentina	United States Dollar	Carlos	International	Analyst	Hours	88.00
Argentina	United States Dollar	Carlos	International	Analyst	Payroll	35,200.00
Argentina	United States Dollar	Carlos	International	Analyst	Cost rate	400.00
Argentina	United States Dollar	Federico	Local	Senior	Hours	30.00
Argentina	United States Dollar	Federico	Local	Senior	Payroll	15,000.00
Argentina	United States Dollar	Federico	Local	Senior	Cost rate	500.00
Argentina	United States Dollar	Federico	International	Senior	Hours	93.00
Argentina	United States Dollar	Federico	International	Senior	Payroll	46,500.00
Argentina	United States Dollar	Federico	International	Senior	Cost rate	500.00

Any idea / Trick ?

Thanks, 

[cristianml]

Hi @parry2k ,

In the following example I need a formula to count a total 2 intead of 5 :

In the example the result should be 2 ( one per "name")

Regards,

[v-frfei-msft · Accepted solution]

Hi @cristianml ,

To create a measure as below.

Measure = 
VAR su =
    SUMMARIZECOLUMNS (
        Sheet1[Location],
        Sheet1[Level],
        Sheet1[Category],
        Sheet1[Name],
        "qua", SUM ( Sheet1[Quantity] )
    )
VAR suf =
    FILTER ( su, [Category] = "Hours" )
RETURN
    IF (
        ISFILTERED ( Sheet1[Location] ),
        CALCULATE (
            COUNT ( Sheet1[Quantity] ),
            FILTER ( Sheet1, Sheet1[Category] = "Hours" && Sheet1[Quantity] > 1 )
        ),
        CALCULATE ( COUNTROWS ( suf ) )
    )

[cristianml]

Hi @v-frfei-msft ,

I just figured out..  I combined your measure with distinctcount and works perfect 🙂  

Headcount = CALCULATE(DISTINCTCOUNT('Resource Actual'[Name]),FILTER('Resource Actual','Resource Actual'[Category]="Hours" && 'Resource Actual'[Quantity]>8))

Thanks :)!!

[cristianml]

Hi @v-frfei-msft ,

Thans for this,  is closer to what I need but how can I see 1 for each name in the column ( by the category line I choose, that in thi case is "Hours") ?  In the example how would be the measure to see 1 instead of 3 ?  i have many filters applied and I would like to see a headcount per "Level" and the number 1 per each name

Thanks

[parry2k]

@cristianml your question is not very clear> What you are trying to count? What you mean by duplicate? Can you explain  your logic again and example output based on the sample data you posted.