Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help created a new column with formula

I have a table called Team Members with columns called prop_id, Allocation, and Role. Unfortunately, Allocation is not always correct in the data source for some situations and it can't be fixed easily there, so I need to use a formula to resolve it during the query. Basically, if there are multiple Team Member records with Role='lead' for a given prop_id, then the Allocation is correct, but if there is only one Team Member record with Role='lead' then Allocation comes in as 0. I need to transform that 0 to 100 for these cases. I want to create a new column called "Corrected Allocation", but I'm not really sure how to write the M code to accomplish this.

 

I think I need to use code to create a temporary table with all records with the current row's prop_id and Role='lead', then evaluate if there is only one row in that table. If so, Corrected Allocation would be set to 100. If there is more than one row, then Corrected Allocation would be set to Allocation.

 

Can someone help me translate that to M?

 

Thanks!

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column as below.

    Column = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Allocation] ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( a ) ) && b = 0, 100, 'Table'[Allocation] )

     

13 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous ,

    Does Allocation come in at 0 for any other situations?

    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, when Role has other values, Allocation may equal zero. I only care about the Team Members with Role='lead'. If Role='admin', Allocation should be zero, but they get filtered out of my report anyway.

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    I need to transform that 0 to 100 for these cases. I want to create a new column called "Corrected Allocation", but I'm not really sure how to write the M code to accomplish this.

    How about using a conditional statement  and change Index to Allocation.

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Anonymous ,

      Then add [Role] =lead

       

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column as below.

    Column = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Allocation] ),
        FILTER (
            'Table',
            'Table'[Role] = "Lead"
                && 'Table'[prop_id] = EARLIER ( 'Table'[prop_id] )
                && 'Table'[Member ID] <= EARLIER ( 'Table'[Member ID] )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( a ) ) && b = 0, 100, 'Table'[Allocation] )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This totally worked. I was trying to do it with M in Power Query, but just creating a custom column with a DAX formula was easy with your instructions.

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous ,

         

        One thing to be aware of is that there is significantly higher overhead using a Calculated Column as opposed to doing this in Power Query.