Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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!
Proud to be a Super User!
Paul on Linkedin.
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.
Proud to be a Super User!
Paul on Linkedin.
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)
Proud to be a Super User!
Paul on Linkedin.
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?
Proud to be a Super User!
Paul on Linkedin.
Yes, my dates table is continues.
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.
From the official DAX reference guide:
so one returns all the dates from the previous year; the other the values within the range of dates selected. I'm not sure why SAMEPERIODLASTYEAR doesn't work in your case, since the date range cover whole years anyway (filtering by year). In the example I posted, both work however.
can you post a screenshot of your date table?
Proud to be a Super User!
Paul on Linkedin.
Hi,
Just found out, my Date table is not continues after all. So this is the cause obviously.
Many thanks for your guidance on this. Much appreciated.
Thanks
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!
Proud to be a Super User!
Paul on Linkedin.
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à!
Proud to be a Super User!
Paul on Linkedin.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |