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.
Hello everyone 🙂
i've been asked to find a way to compare customers activity between two years.
I don't need to have the exact values of their purchases but i have to show if between one time interval to an another they have been working with us. ( From 2016 to 2017).
Three answer can be possible :
I'm a beginner with DAX language but i would like to find a way to exprim my request.
If you need more information about my request you can ask me futher details.
Best regards
Kurumy
You may add measures as shown below.
new = COUNTROWS ( DISTINCT ( EXCEPT ( SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] ), SELECTCOLUMNS ( FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ), "customerId", Table1[customerId] ) ) ) )
steady = COUNTROWS ( DISTINCT ( INTERSECT ( SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] ), SELECTCOLUMNS ( FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ), "customerId", Table1[customerId] ) ) ) )
stopped = COUNTROWS ( DISTINCT ( EXCEPT ( SELECTCOLUMNS ( FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ), "customerId", Table1[customerId] ), SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] ) ) ) )
Hi
Thanks that what i wanted !
But i have a problem the measure "Stopped" is not working. It only show me "ALL" and don't integrate the filter.
Do you have a solution for that ?
Thanks
Please take a closer look at measures new and stopped. The only difference is the order of the two expressions for EXCEPT Function.
I saw that.
As long as i understand in the "New" measure you used the function EXCEPT because you compared the orginal table with a filtred table ( with a precise year). If there is a difference between theses two tables inside the customers ID colonn, the functions count as 1. Am i Right ?
In your formula when you use "-1", is that for choose a filtrer with a the previous year ( year n-1) ?
The data i use is the aggregation of data from 2015,2016,2017. is it possible to use the function except by compare a filtred table from a precise year (2015 ;2016 or 2017) with the original one ?
thanks
Does someone have a solution ?
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 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |