Forum Discussion
Need help on Calculate function
Hi guys,
Need some 'enlightment'.
As I have these tables and these relationship :
If I want to get numbers of work orders with this Calculate function, is this correct?
Work orders count = CALCULATE(
COUNTROWS('Work orders'),
'Timesheet'
)And to get previous year count, I'm using this formula:
Work orders PY = CALCULATE(
[Work orders count],
SAMEPERIODLASTYEAR(Dates[Date])
)
At this point actually I know it is not right because I get the wrong value, but I don;t know what's wrong and what is the correct formula.
The result now is like this:
Whereby if I'm summarize directly to 'Timesheet' table which is the detail transactions table and using the bulitin Power BI Count feature (drag the WO Id column to table then choose Count), I will get this :
Understand there is some WO Id duplicates in the Timesheet tables which is allowed, but at least those are the figures just to show my data.
Please advice.
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!
13 Replies
To get the numer of work order (distinct), you need:
Work Orders = DISTINCTCOUNT(Timesheet[WorkID])
And for the previous year's:
PY Work Orders = CALCULATE([Work Orders], PREVIOUSYEAR(Dates[Date])Create a table, add the Year and both measures et voilà!
Hi,
Thanks, but for my knowledge, may I know why I can't COUNT Works Orders at the first measure?
Noted about the distinct, although at my scenario actually I won't mind about duplicates.
For the YEAR, I had, btw, but thanks.
Thanks,
As usual, you have anumber of options in DAX. You can use COUNT (which counts the number of values in a column), COUNTROWS (which counts the number of rows in a table) or DISTINCTCOUNT (Which counts the number of disctinct values in a column). You only need CALCULATE if you need to modify the evaluation context.
Sorry, just for a better understanding, means to say I really cannot use this formula ?
Work orders count = CALCULATE(COUNTROWS('Work orders'), 'Timesheet')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
