Dax: blank values +0 formula

[Anonymous]

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 ?

[Iamnvt · Accepted 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"
)

[BekahLoSurdo · Accepted solution]

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!

[Anonymous]

@Iamnvt responded before you but you gave me the explaination so I have accepted both of you as a solution. Thanks guy !

[Iamnvt · Accepted 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"
)