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.
I created a calculated column base on date like so :
Répartition d'activation = SWITCH( TRUE(), Associations[first_payment_date] = BLANK(), BLANK(), DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) < 0 , 0, DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) >= 0 , DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) )
Then from this column I made another calculated column to made group as requested by my coleagues :
Groupe répartition activation = SWITCH( TRUE(), Associations[Répartition d'activation] = BLANK(), "None", Associations[Répartition d'activation] = 0, "J+0", Associations[Répartition d'activation] > 0 && Associations[Répartition d'activation] <= 14, "J+1 - J+14", Associations[Répartition d'activation] > 14 && Associations[Répartition d'activation] <= 30, "J+15 - J+30", Associations[Répartition d'activation] > 30 && Associations[Répartition d'activation] <= 45, "J+31 - J+45", Associations[Répartition d'activation] > 45, "J+46 et +" )
Unfortunnally if the value of Associations[Répartition d'activation] = 0, the script class it as Blank() and add the value None instead of 0.
Here you can see the result for the 3rd row for exemple.
How can I fix that ?
Solved! Go to Solution.
you can try the following:
Groupe répartition activation = SWITCH( TRUE(), NOT(ISBLANK(Associations[Répartition d'activation])) && Associations[Répartition d'activation] = 0, "J+0", Associations[Répartition d'activation] > 0 && Associations[Répartition d'activation] <= 14, "J+1 - J+14", Associations[Répartition d'activation] > 14 && Associations[Répartition d'activation] <= 30, "J+15 - J+30", Associations[Répartition d'activation] > 30 && Associations[Répartition d'activation] <= 45, "J+31 - J+45", Associations[Répartition d'activation] > 45, "J+46 et +",
Associations[Répartition d'activation] = BLANK(), "None" )
Hi @Anonymous,
The SWITCH() tests for equality in order (so if a column could be applied to more than one of the parameters, it will be applied to the one listed first). Because a 0 value can = 0 and can also = BLANK(), move the one that you'd like applied to the front:
Répartition d'activation = SWITCH( TRUE(), DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) >= 0 , DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY), Associations[first_payment_date] = BLANK(), BLANK(), DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) < 0 , 0 )
Hope this helps!
Hi @Anonymous,
The SWITCH() tests for equality in order (so if a column could be applied to more than one of the parameters, it will be applied to the one listed first). Because a 0 value can = 0 and can also = BLANK(), move the one that you'd like applied to the front:
Répartition d'activation = SWITCH( TRUE(), DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) >= 0 , DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY), Associations[first_payment_date] = BLANK(), BLANK(), DATEDIFF(Associations[date_inscription], Associations[first_payment_date] ,DAY) < 0 , 0 )
Hope this helps!
@Iamnvt responded before you but you gave me the explaination so I have accepted both of you as a solution. Thanks guy !
you can try the following:
Groupe répartition activation = SWITCH( TRUE(), NOT(ISBLANK(Associations[Répartition d'activation])) && Associations[Répartition d'activation] = 0, "J+0", Associations[Répartition d'activation] > 0 && Associations[Répartition d'activation] <= 14, "J+1 - J+14", Associations[Répartition d'activation] > 14 && Associations[Répartition d'activation] <= 30, "J+15 - J+30", Associations[Répartition d'activation] > 30 && Associations[Répartition d'activation] <= 45, "J+31 - J+45", Associations[Répartition d'activation] > 45, "J+46 et +",
Associations[Répartition d'activation] = BLANK(), "None" )
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |