Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date columns in IF function

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_le...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    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!

     

amitchandak's avatar
amitchandak
Super User
4 years ago

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

)