Forum Discussion

Jack_D's avatar
Jack_D
Frequent Visitor
1 year ago
Solved

Calculated column question

HI All 
I have a challenge task that required some help. 

My dataset is located on the left, and my desired output logic involves marking only the first action for each product. By doing so, I can calculate the number of first actions per product.

Thank you in advance for your kind assistance. 😊

Jack

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ValtteriN ,thanks for the quick reply, I'll add more.

    Hi Jack_D ,

    The Table data is shown below:

    Please follow these steps:

    1.Creating an index column in Power Query

     

    Table.AddIndexColumn([Column],"Index",1)

     

    2.Use the following DAX expression to create a column(The data type of the 'Index' column is number)

    Column = 
VAR _Product_type = [Product type1]
VAR _1st = MAXX(FILTER('Table',[Product type1] = _Product_type && [Index] = 1),[Actions])
VAR _2nd = MINX(FILTER('Table',[Product type1] = _Product_type && [Actions] <> _1st ) ,[Index])
RETURN IF([Index] >= _2nd,FALSE(),TRUE())

    3.Use the following DAX expression to create a measure

    Measure = 
COUNTROWS(FILTER('Table',[Column]  = TRUE()))

    4.Final output

    Best Regards,
    Wenbin Zhou

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ValtteriN ,thanks for the quick reply, I'll add more.

    Hi Jack_D ,

    The Table data is shown below:

    Please follow these steps:

    1.Creating an index column in Power Query

     

    Table.AddIndexColumn([Column],"Index",1)

     

    2.Use the following DAX expression to create a column(The data type of the 'Index' column is number)

    Column = 
VAR _Product_type = [Product type1]
VAR _1st = MAXX(FILTER('Table',[Product type1] = _Product_type && [Index] = 1),[Actions])
VAR _2nd = MINX(FILTER('Table',[Product type1] = _Product_type && [Actions] <> _1st ) ,[Index])
RETURN IF([Index] >= _2nd,FALSE(),TRUE())

    3.Use the following DAX expression to create a measure

    Measure = 
COUNTROWS(FILTER('Table',[Column]  = TRUE()))

    4.Final output

    Best Regards,
    Wenbin Zhou

     

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    1 year ago

    Hi,

    This should give you the desired result:

    First =
    IF(
    RANKX(
        FILTER(
            ALL('Table (40)'),
            'Table (40)'[Product] = EARLIER('Table (40)'[Product])
        ),
        [Date],
        ,
        ASC
    )=1,1,BLANK())

    End result:

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/



    • Jack_D's avatar
      Jack_D
      Frequent Visitor
      1 year ago

      Thanks Valtterin
      I am trying to calculated Sum of All first actions so the out put will be