Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |