topic Re: Date columns in IF function in DAX Commands and Tips

Date columns in IF function

Anonymous — Tue, 09 Nov 2021 07:02:42 GMT

Hi!

I have two date columns:

•dim_leads[meeting_date]
•dim_leads[closed_date]

What I want is to:

if dim_leads[closed_date] = BLANK(),

then calculate the number of leads according to dim_leads[meeting_date], otherwise according to dim_leads[closed_date].

I've tried this:

IF(

dim_leads[closed_date] = BLANK(),

CALCULATE(

DISTINCTCOUNT(dim_leads[id]),

USERELATIONSHIP(dim_date[Date], dim_leads[meeting_date])

CALCULATE(

DISTINCTCOUNT(dim_leads[id]),

USERELATIONSHIP(dim_date[Date], dim_leads[closed_date])

)

... but the thing is that it returns me the error because IF can not define a single value from date column and requires me to specify an aggregate which is not what i'm looking for.

Can you help me with this, please? How can I combine IF function with date column in it?

Re: Date columns in IF function

amitchandak — Tue, 09 Nov 2021 08:52:49 GMT

Anonymous , Try like

CALCULATE(

DISTINCTCOUNT(dim_leads[id]),filter(dim_leads, isblank(dim_leads[closed_date])),
USERELATIONSHIP(dim_date[Date], dim_leads[meeting_date]) )
+
CALCULATE(

DISTINCTCOUNT(dim_leads[id]),filter(dim_leads, not(isblank(dim_leads[closed_date])) ),
USERELATIONSHIP(dim_date[Date], dim_leads[closed_date])

)

Re: Date columns in IF function

v-cazheng-msft — Thu, 11 Nov 2021 07:07:45 GMT

Hi Anonymous

Please make a little bit change to your dax formula.

CountIdInMeasure =

IF (

SELECTEDVALUE ( dim_leads[closed_date] ) = BLANK (),

CALCULATE (

DISTINCTCOUNT ( dim_leads[id] ),

USERELATIONSHIP ( dim_date[Date], dim_leads[meeting_date] )

CALCULATE (

DISTINCTCOUNT ( dim_leads[id] ),

USERELATIONSHIP ( dim_date[Date], dim_leads[closed_date] )

)

Result will looks like this:

Attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Date columns in IF function

Anonymous — Wed, 17 Nov 2021 10:52:52 GMT

Hey, thank you so much, you really helped me out! I appreciate it a lot.