Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
5 years ago
Solved

Dax Help needed to create a calculated coloumn

Dear Experts,   I have a table called Mercury_Extract I need a calculated coloumn (Standard Available Hours) which ignores all repeated lines  except one and gives me a output as 40 It should ide...
Need Help
Show and Tell
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    5 years ago

    Hi vjnvinod,

     

    Ignore duplicate rows and only output the results for one of them. I have a method here that is worth a try, you need to create an index column first.

    In the power query editor-> add column->index column -> close and apply

    Then use this formula:

    give you 40 =
VAR _1 =
    CALCULATE(
        MIN( [Index] ),
        FILTER(
            'Mercury_Extract',
            [Attribute] = EARLIER( Mercury_Extract[Attribute] )
                && [Resource GPN] = EARLIER( Mercury_Extract[Resource GPN] )
        )
    )
RETURN
    IF( [Index] = _1, 40, 0 )

     

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi vjnvinod 

You can't create a new column with just 1 row.  Columns wil have as many rows in them as the entire table.

You might need a measure instead which will give you a single value, but I still don't understand what you want, your explanatrion isn't clear enough I'm afraid.

 

1. How are you calculating the value 40?

2. Why is 40 relevant?

3. You say "hence if there are 22 rows, Standard available hours should only be in 1 row" - why?  What's the relevance of this statement?

 

You still haven't provided data, hard to do anything without it I'm afraid.

Regards

Phil

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
5 years ago

PhilipTreacy 

 

Hi,

 

here is the sample data

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

 

1. How are you calculating the value 40? : This is standard available hours for a resource in a week (for instance if a resource worked in a week for 5 days, that means 8*5=40 hours

2. Why is 40 relevant?: 40 is relevant because i need to calculate utilization% of resource

3. You say "hence if there are 22 rows, Standard available hours should only be in 1 row" - why?  What's the relevance of this statement?

Because these lines are repeating because resources has filled timesheets for various codes, hence it might have more thn 1 rows

So i need a formula or code which checks coloumn "Resource GPN" & "attribute" and if the rows greater thn >1, lets say 22 lines, only 1st lines shows as "40" and remaining 21 lines "0"

 

as an output i am expecting as below

Resource GPNUpdated onAttributeChargableStandard Available Hours
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 202124.0040.00
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 20210.000.00
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 20210.000.00
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 20210.000.00
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 20210.000.00
AE01112031010-Jun-21Time (Hours) 11 Sep 2021 - 17 Sep 20210.000.00

 

Hope i am able to get some more clarity

 

  • vjnvinod's avatar
    vjnvinod
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    PhilipTreacy 

     

    is there anyway we can achieve this? i have search all over the internet, not finding a solution