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!
Some important thing for me to understand is actually for what is the 2nd parameter in that formula I'm using ?
I thought because I mentioned 'Timesheet' and this table has relationship to Dates table with Year column in it, it will suggest the formula of the Year relations.
Sorry, I just really need to understand more on this function. The more I look into several resources, docs, courses or Youtube, the more I get confuse.
Thanks
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!