Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtracting LAST ID in same column

IDAgeAtEventEventTypeDifferenrce 
101101st Tag-
102101st Tag-
103101st Tag-
101162nd tag6
104101st Tag-
105111st Tag 
103142nd tag3
101203rd Tag4

 

Best way to achieve this output in Power BI? I have the 1st 3 columns, looking to calculate the 4th

 

 

Essentially, each row represents an event trigger, and I would like to find the difference of event age. So 2nd Event minus 1st event, but if no other event comes before it, then prdouce a blank. 

 

ID 101 for example, has 3 events, so I would like the difference from 1st event to 2nd, 2nd to 3rd, etc. 

 

Thank you in advance!

  • Nathaniel_C's avatar
    Nathaniel_C
    4 years ago

    Hi Anonymous ,

    Like this?

     

     

     


    Let me know if you have any questions.

    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
    4 years ago

    Hi Anonymous ,

    Please try this:

    mydifcolumn = 

var _ID = (TagTable[ID])
var _ageAtEvent = (TagTable[AgeAtEvent])
var _EventTypeOnly = (TagTable[EventType # only])

var _previousEventTypeOnly = _EventTypeOnly-1 //get the previous Event Type

var _calc = CALCULATE(MAX(TagTable[AgeAtEvent]),FILTER(all(TagTable),TagTable[EventType # only]=_previousEventTypeOnly && TagTable[ID]=_ID)) // Get the Age At Event for the previous row of the same ID

Return if (_previousEventTypeOnly>0, _ageAtEvent- _calc)

     

    Basically the same thing, but take out the MAX() surrounding all the column names that are brought in as variables (var)

    Good Luck! And as this is a second solution, would you please mark this as well?


    Let me know if you have any questions.

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Yes! Thank you Nathaniel_C .

     

    I have an EventType #Only column as well! How did you make this work? 

     

    Best,

     Senad

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

      You are welcome! Did my second post answer your question Anonymous ? Nathaniel

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Nathaniel_C  this works great as a measure, thank you so much

     

    Is there a way to do it as a calculated column, though?


    Let me know if you can!

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

      Hi Anonymous ,

      Please try this:

      mydifcolumn = 

var _ID = (TagTable[ID])
var _ageAtEvent = (TagTable[AgeAtEvent])
var _EventTypeOnly = (TagTable[EventType # only])

var _previousEventTypeOnly = _EventTypeOnly-1 //get the previous Event Type

var _calc = CALCULATE(MAX(TagTable[AgeAtEvent]),FILTER(all(TagTable),TagTable[EventType # only]=_previousEventTypeOnly && TagTable[ID]=_ID)) // Get the Age At Event for the previous row of the same ID

Return if (_previousEventTypeOnly>0, _ageAtEvent- _calc)

       

      Basically the same thing, but take out the MAX() surrounding all the column names that are brought in as variables (var)

      Good Luck! And as this is a second solution, would you please mark this as well?


      Let me know if you have any questions.

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Yes! Thank you so much. Appreciate the explanation as well. Have a good one

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

    Hi Anonymous ,

    Like this?

     

     

     


    Let me know if you have any questions.

    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
      4 years ago

      Hi Anonymous ,
      I would note that the value you have for 103 Difference should be 14-10=4.

      mydif = 

var _ID = max(TagTable[ID])
var _ageAtEvent = MAX(TagTable[AgeAtEvent])
var _EventTypeOnly = MAX(TagTable[EventType # only])

var _previousEventTypeOnly = _EventTypeOnly-1 //get the previous Event Type

var _calc = CALCULATE(MAX(TagTable[AgeAtEvent]),FILTER(all(TagTable),TagTable[EventType # only]=_previousEventTypeOnly && TagTable[ID]=_ID)) // Get the Age At Event for the previous row of the same ID

Return if (_previousEventTypeOnly>0, _ageAtEvent- _calc)

       

      Previous to creating this measure, I extracted the numeric value in Power Query from the column EventType to a new column EventType # Only and made it a whole number.

       


      Let me know if you have any questions.

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