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.
Dear all,
I would like to calculate the segment movement of customers between two dates, but I'dont have any idea how to model or wich dax formula to use. I have following table:
The goal is to get a cross table by using following slicers:
Let me explain on two customers how the desired cross table should be calculated.
Customer 1:
- Classified as A in 2018
- Classified as B in 2019
Customer 3:
- Doesn't exist in 2018
- Classified as B in 2019
My question to you: How can I realize that? Is there already a DAX pattern (I googled it but unfortunately I couldn't find anything)?
Thank you in advance.
Best regards
Semih
Solved! Go to Solution.
Hi @Semih87,
I can think of a few possibilities on how to solve this, although some of them will only work if your dataset is fairly small.
I have created a report based on your sample data and requirements: report, using one of the possibilities. This one does not involve any tampering with the table, but it needs 4 extra tables/dimensions, all with one single column: year_to, year_from, classification_to and classification_from. The year-tables must contain all years from the table, while the classification-tables must contain all the segments. In addition, the classification_to-table needs a row with the value "Lost", and the classification_from-table needs "New". These 4 tables connects to the main table with 1:many-relationships.
After that everything is handled by a measure. The DAX-code is a bit lengthy, but it is not very complicated DAX, so don't let it scare you.
I have only tested it on the data you provided, you should probably test it on a larger dataset.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
https://www.dropbox.com/s/olq93ij5y3tgzjz/Segment%20movement%20between%20two%20dates.pbix?dl=0
Hi @Semih87,
I can think of a few possibilities on how to solve this, although some of them will only work if your dataset is fairly small.
I have created a report based on your sample data and requirements: report, using one of the possibilities. This one does not involve any tampering with the table, but it needs 4 extra tables/dimensions, all with one single column: year_to, year_from, classification_to and classification_from. The year-tables must contain all years from the table, while the classification-tables must contain all the segments. In addition, the classification_to-table needs a row with the value "Lost", and the classification_from-table needs "New". These 4 tables connects to the main table with 1:many-relationships.
After that everything is handled by a measure. The DAX-code is a bit lengthy, but it is not very complicated DAX, so don't let it scare you.
I have only tested it on the data you provided, you should probably test it on a larger dataset.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
https://www.dropbox.com/s/olq93ij5y3tgzjz/Segment%20movement%20between%20two%20dates.pbix?dl=0
You Sir, saved my day. This approach works for me well on 10^5 rows.
Hello @sturlaws,
That's magic! Thank you very much. I will test the solution on a larger dataset next week.
Best regards
Semih
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 |
---|---|
111 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |