Forum Discussion
Need help on Calculate function
Understanding how CALCULATE works is fundamental in DAX. CALCULATE allows you to remove the filter context - if you need to - and apply further filters. The function brekas down into "two sections":
CALCULATE( expression, filters)
Expression is what you are calculating; filters is the rows you want the calculation to be carried out (using filters or table references),
In you case, your measure is:
Work orders count = CALCULATE(
COUNTROWS('Work orders'),
'Timesheet'
)So the expression in red is what you want to calculate, and the table reference is the filter you want the calculation to be applied to (in other words, which rows to make the calculation on).
In your model, the table 'Work orders" is a dimension table. If you simply counted the rows of this table, you would get the toal number of rows in the table for all the years, since it is unrleated to the Date table. By adding the "Timesheet" filter expression in the CALCULATE function, you are saying you want the COUNTROWS to be filtered according to the filters applied to the Timesheet table (which is filtered by the Year field.
To see the difference I've used a sample dataset similar to your structure. This is the model:
Now compare these two measures:
Countrows Dim Items = COUNTROWS('Dim Item')and (similar to your measure)
Calc Countrows w DataTable = CALCULATE(COUNTROWS('Dim Item'), 'DataTable')In a table you get this:
The first measure (your example), is counting the rows filtered by the Year field.
Just so that you can see the DISTINCTCOUNT also:
I hope that helps a bit. It is very important that you understand how CALCULATE works!
Sorry, just for a better understanding, means to say I really cannot use this formula ?
Sure you can! Your measure delivers the same result as DISTINCTCOUNT(Timesheet[Work Id]).
(I think the DISTINCTCOUNT is easier to read though)
No, the measure is wrong especially when I re-use the measure for the previous year measure using SAMEPERIODLASTYEAR function.
like the 1st image I attach:
so basically, again it is only for my better understanding (doesn't mean I didn't want to follow your suggestion), what I want to know is:
1. Why my 1st measure is not working ? especially when re-use it for Previous year measure.
2. What is actually the use of 2nd parameter of my Calculate function, the 'Timesheet' as I thought it will then connected and 'consider' Dates[Year] which is the relation between Timesheet and Dates.
3. Understood for 'more readable' thing, but this measures is created by us (or me) so I need to understand this first, more than it is for readable sake. Reason is I have the Work Orders table already as the master table, and for my case this time I want to count to master table instead. Another reason also the master table already unique Id, so to count master table, I don't have to use Distinct.
So, appologize, at this moment, I need more of an undestanding rather than other formula works.
Thanks.
To get the values for the previous year, use PREVIOUSYEAR(Date[Date]) instead of SAMEPERIODLASTYEAR
PY Calc Countrows = CALCULATE([Calc Countrows w DataTable], PREVIOUSYEAR('Calendar Table'[Date]))Edit:
In my sample, SAMEPERIODLASTYEAR also works:
SPLY Calc Countrows = CALCULATE([Calc Countrows w DataTable], SAMEPERIODLASTYEAR('Calendar Table'[Date]))Does your date table have continuous dates covering the range of dates in the model?
Hi,
Thank you very much for your kind explanation. Now I have better understanding. And yes, after using PREVIOUSYEAR(), it is correct now.
Mind for last question ? (really it is my last question), 😁 why SAMEPERIODLASTYEAR is not working ?
As I tried to not using too many function, it seems too many similar function but each has its own tricks.
Thanks.