Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count of Days in a Measure instead of Calculated Column

Number of Days in Care = IF(ISBLANK([END_DATE]),DATEDIFF([START_DATE],TODAY(),DAY),DATEDIFF([START_DATE],[END_DATE],DAY))
 
Please can someone assist me with the above?
 
I need the same logic above to be put translated into a measure, as the calculated column is significantly impacting performance in Power BI.
 
I have a table with the columns 'PERSON_ID, START_DATE, END_DATE'. I need to have a measure that gives me the following:
 
PERSON_ID with START_DATE and END_DATE, therefore give me number of days
PERSON_ID with START_DATE and no END_DATE, therefore give me number of days from START_DATE to today.
 
Thanks,
Dan.
1 ACCEPTED SOLUTION

Hey @Anonymous ,

 

based on my sample data:

TomMartens_0-1597315628323.png

here is the correct measure, be aware of the slightly changed first parameter of the SUMX function:

measure name = 
SUMX(
	VALUES('table')
	,var _today = TODAY()
	var _startdate = [START_DATE]
	var _enddate = [END_DATE]
	var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
	return
	DATEDIFF(_startdate,__enddate,DAY)
)

my table visual:

TomMartens_1-1597315783137.png

I doubt that the measure approach will speed up the loadig of the visual.

 

A calculated will evalutated just once, this happens when the data model is loaded. If we'are talking about Power BI Desktop, this happens when you open the report, when you refresh the underlying data, or when you change a calculatio.

 

Indeed, a measure will be only evelauted when it's needed, so basically using a measure should be used instead of a calculated column, as the overall memory footpring will be smaller using a measure.

 

But, in this special case, if you are aiming for speeding up the load time of a specific visual, my assumption is that, the calculated column will be faster than the measure, as the value of the calculated columns just has to be visualized where the measure has to be evaluated and then visualized.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

10 REPLIES 10
TomMartens
Super User
Super User

Hey @Anonymous ,

 

you should consider this DAX for a calculated columns as this is using variables to avoid multiple column evaluations inside the IF

column name = 
var _today = TODAY()
var _startdate = [START_DATE]
var _enddate = [END_DATE]
var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
return
DATEDIFF(_startdate,__enddate,DAY)

As the Start- and Enddate can be considered being properties of the PERSON_ID the measure will look similar:

measure name =
SUMX(
	VALUES('tablename'[PERSON_ID])
	,var _today = TODAY()
	var _startdate = [START_DATE]
	var _enddate = [END_DATE]
	var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
	return
	DATEDIFF(_startdate,__enddate,DAY)
)

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi @TomMartens ,

 

Thanks for this but unfortunately the calculated column didn't work, it returned the same value for every single PERSON_ID. I couldn't get the measure to work at all due to syntax. I believe you understand my issue but it seems I have not been very clear, given other users' comments. To clarify:

 

I have a calculated column with this current DAX:

Number of Days = IF(ISBLANK([END_DATE]),DATEDIFF([START_DATE],TODAY(),DAY),DATEDIFF([START_DATE],[END_DATE],DAY))

 

It produces this current data table (which is 'PersonTable') :

 

PERSON_IDSTART_DATEEND_DATENumber of Days (calculated column)
123 July 200430 July 20047
210 April 200410 April 2005365
310 August 2020(blank)2

 

I have a table visualisation in my Power BI report page with PERSON_ID and Number of days as values. I have a filter so that I only have with PERSON_ID with '(blank)' END_DATE in the table:

PERSON_IDNumber of Days (Calculated column)
32

My calculated column works, but it is going through every single row and is causing the visualisation to take a very long time to load.

 

I hope this is clear and thanks everyone for your inputs @amitchandak @Greg_Deckler 

Hi @Anonymous ,

 

Would you please refer to the measure below:

 

 

Measure = IF(ISBLANK(MAX('Table'[END_DATE])),DATEDIFF(MAX('Table'[START_DATE]),TODAY(),DAY),DATEDIFF(MAX('Table'[START_DATE]),MAX('Table'[END_DATE]),DAY))

 

 

Capture4.PNG 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Anonymous
Not applicable

Hi @v-deddai1-msft ,

 

the measure doesn't work as it returns the same number of days for every PERSON_ID, eg:

 

PERSON_IDNumber of Days Measure
1100
2100
3100
4100

Hey @Anonymous ,

 

based on my sample data:

TomMartens_0-1597315628323.png

here is the correct measure, be aware of the slightly changed first parameter of the SUMX function:

measure name = 
SUMX(
	VALUES('table')
	,var _today = TODAY()
	var _startdate = [START_DATE]
	var _enddate = [END_DATE]
	var __enddate = IF(NOT(ISBLANK(_enddate)) , _enddate , _today)
	return
	DATEDIFF(_startdate,__enddate,DAY)
)

my table visual:

TomMartens_1-1597315783137.png

I doubt that the measure approach will speed up the loadig of the visual.

 

A calculated will evalutated just once, this happens when the data model is loaded. If we'are talking about Power BI Desktop, this happens when you open the report, when you refresh the underlying data, or when you change a calculatio.

 

Indeed, a measure will be only evelauted when it's needed, so basically using a measure should be used instead of a calculated column, as the overall memory footpring will be smaller using a measure.

 

But, in this special case, if you are aiming for speeding up the load time of a specific visual, my assumption is that, the calculated column will be faster than the measure, as the value of the calculated columns just has to be visualized where the measure has to be evaluated and then visualized.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens  OK thanks for the advice, I will leave as a calculated column based on your input! 

 

FYI the measure works too 😎

Hey @Anonymous ,

 

prepare a pbix with sample data, upload the pibix to onedrive or dropbox and share the link (make sure that start date and end date date have a date or datetime data type). If you are using an xlsx to create the sample data and share the xlsx as well.


Hmm, wondering what is going as you can see, the DAX statement creates different values, based on admittedly small sample data:

TomMartens_0-1597259456514.png

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens 

 

thanks so much for your help. Today I have tried the calculated column and for some reason it works absolutely fine now, thanks! This will certainly help performance issues and is a great temporary fix. Now I would like to progress to a measure, as I believe this will further decrease the performance time to load the visualisation.

 

The measure however has not been succesful. Note I had to edit 'var _startdate' and 'var _enddate' as they needed to have a MAX/MIN function. Please see the screenshot below:

 

dwidrascuBG_0-1597307359447.pngdwidrascuBG_1-1597307432891.png

As you can see, it will also not take '_enddate' and '__endate'

 

I hope this means we no longer need the sample data and pbix file, but please let me know if this is still the case.

 

Thanks very much Tom!

 

amitchandak
Super User
Super User
Greg_Deckler
Super User
Super User

@Anonymous So generally you convert a column to a measure by adding aggregatins like MAX/MIN, etc. to column references. That is very general and depends on context. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.