Forum Discussion

Jack_D's avatar
Jack_D
Frequent Visitor
2 years 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, ...
  • Anonymous's avatar
    Anonymous
    2 years 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

     

Anonymous's avatar
Anonymous
Not applicable
2 years 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