cancel
Showing results for
Did you mean:
Frequent Visitor

## 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:

 TransactionDate Customer ID Product 1.2.2019 A X 5.2.2019 A X 16.2.2019 A Y 4.2.2019 B X 20.2.2019 B Y 6.3.2019 A X

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:

 Index Start of period End of period Customer Number of purchases X Number of purchases Y 1 1.2.2019 28.2.2019 A 2 1 2 1.2.2019 28.2.2019 B 1 1 3 1.3.2019 31.3.2019 A 1 0

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!

1 ACCEPTED SOLUTION Microsoft

Hi @biz_wiz ,

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2 Microsoft

Hi @biz_wiz ,

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thank you so much Yuliana 🙂 @v-yulgu-msft   