cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gilHA Regular Visitor
Regular Visitor

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.

PBI_NoneValue.png

 

 

Here you can see the result for the 3rd row for exemple.

How can I fix that ?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Iamnvt Member
Member

Re: Dax: blank values +0 formula

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" )

View solution in original post

Re: Dax: blank values +0 formula

Hi @gilHA,

 

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!

View solution in original post

3 REPLIES 3
Iamnvt Member
Member

Re: Dax: blank values +0 formula

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" )

View solution in original post

Re: Dax: blank values +0 formula

Hi @gilHA,

 

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!

View solution in original post

gilHA Regular Visitor
Regular Visitor

Re: Dax: blank values +0 formula

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 320 members 3,232 guests
Please welcome our newest community members: