Forum Discussion
Dax: blank values +0 formula
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 ?
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!
3 Replies
IamnvtContinued Contributor
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" )
BekahLoSurdoResolver IV
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 !