Forum Discussion
Impactful IndividualStandard available hours DAX
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
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.
3 Replies
vjnvinodis that something you can help with?
Appears like to be a difficult one to achieve
i have also provided the sample pbix too
- vjnvinod
Hello Microsoft community team,
Can you please help me with my thread?
is there anyway to achieve this?
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.