Standard available hours DAX

[vjnvinod]

Dear all,

here is my sample pbix

https://drive.google.com/file/d/1-rK9-gNVg2OD6TvKYv8XaGUsUBmKK0sj/view?usp=sharing

 and expected output below

Resource GPN	Updated on	Attribute	Chargable	Standard Available Hours
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	24.00	40.00
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	0.00	0.00
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	0.00	0.00
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	0.00	0.00
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	0.00	0.00
AE011120310	10-Jun-21	Time (Hours) 11 Sep 2021 - 17 Sep 2021	0.00	0.00

Basically, what i need is a calculated coloumn with a logic that if the combination of coloumn Resource GPN and Attribute count is greater thn 1, thn only 1 row should show 40 and the remaining should show me output as 0 (as you can see from the above excel table)

is there anyway to achieve, i am not able to find any solution in the internet to get a dax code to solve this use case

[v-eqin-msft · Accepted solution]

Hi @vjnvinod ,

Based on my test, there are duplicate rows with almost exactly the same value, so you need to add an Index column in Power Query firstly the identify the difference.

And then use the following DAX formula to create a column:

Standard Available Hours =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Mercury_Extract' ),
        ALLEXCEPT (
            Mercury_Extract,
            'Mercury_Extract'[Resource GPN],
            Mercury_Extract[Attribute]
        )
    )
VAR _first =
    MINX (
        FILTER (
            'Mercury_Extract',
            [Resource GPN] = EARLIER ( [Resource GPN] )
                && [Attribute] = EARLIER ( [Attribute] )
        ),
        [Index]
    )
RETURN
    IF (
        _count > 0,
        LOOKUPVALUE ( Mercury_Extract[Attribute], [Index], _first ),
        0
    )

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vjnvinod]

@amitchandak 

is that something you can help with?

Appears like to be a difficult one to achieve

i have also provided the sample pbix too

[vjnvinod]

@v-henryk-mstf 

Hello Microsoft community team,

Can you please help me with my thread?

is there anyway to achieve this?