cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

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!

 

 

1 ACCEPTED SOLUTION
Microsoft
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)

1.PNG

 

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

2.PNG

 

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.

View solution in original post

2 REPLIES 2
Microsoft
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)

1.PNG

 

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

2.PNG

 

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.

View solution in original post

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors