Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to Solution.
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!
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!
Hey, thank you so much, you really helped me out! I appreciate it a lot.
@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])
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |