Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX: Index number based on two columns

Hi,

 

I'm still learning DAX and would really appreciate some support on creating an index number in DAX based on two columns in the data.

 

Data looks like this:

TransactionDateCustomer IDProduct
1.2.2019AX
5.2.2019AX
16.2.2019AY
4.2.2019BX
20.2.2019BY
6.3.2019AX

 

The desired output is a table visual with an index number based TransactionDate (group rows by month) and Customer ID (group by unique ID)

 

Desired output, a table visual looks like this:

 

IndexStart of periodEnd of periodCustomerNumber of purchases XNumber of purchases Y
11.2.201928.2.2019A21
21.2.201928.2.2019B11
31.3.201931.3.2019A10

 

As you can see, row 3 in the table visual is the same customer A but the month is March, so a new index number (3) is assigned.

 

The last two columns I can do with measures and the date attributes I can do with calculated columns using EOMONTH function.

 

Any hints on how this index number could be done in DAX? Thanks for any input!

 

 

Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    Add two calculated columns in source table.

    Start of period =DATE ( YEAR ( Sample1[TransactionDate] ), MONTH ( Sample1[TransactionDate] ), 1 )

End of period = EOMONTH(Sample1[Start of period],0)

     

    New measures.

    index based = SELECTEDVALUE(Sample1[Start of period])&SELECTEDVALUE(Sample1[Customer ID])

Index = RANKX(ALLSELECTED(Sample1),[index based],,ASC,Dense)

Number of purchases X = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="X")+0

Number of purchases Y = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="Y")+0

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous ,

     

    Add two calculated columns in source table.

    Start of period =DATE ( YEAR ( Sample1[TransactionDate] ), MONTH ( Sample1[TransactionDate] ), 1 )

End of period = EOMONTH(Sample1[Start of period],0)

     

    New measures.

    index based = SELECTEDVALUE(Sample1[Start of period])&SELECTEDVALUE(Sample1[Customer ID])

Index = RANKX(ALLSELECTED(Sample1),[index based],,ASC,Dense)

Number of purchases X = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="X")+0

Number of purchases Y = CALCULATE(COUNT(Sample1[Product]),Sample1[Product]="Y")+0

     

    Best regards,

    Yuliana Gu