Need help on Calculate function

[admin_xlsior]

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

[PaulDBrown]

@admin_xlsior 

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.

[admin_xlsior]

Sorry, just for a better understanding, means to say I really cannot use this formula ?

Work orders count = CALCULATE(

COUNTROWS('Work orders'), 'Timesheet'

)

[PaulDBrown]

@admin_xlsior 

Sure you can! Your measure delivers the same result as DISTINCTCOUNT(Timesheet[Work Id]).
(I think the DISTINCTCOUNT is easier to read though)

[admin_xlsior]

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. 

[PaulDBrown]

@admin_xlsior 

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?

[admin_xlsior]

Yes, my dates table is continues. 

[admin_xlsior]

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.

[PaulDBrown]

@admin_xlsior 

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?

[admin_xlsior]

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

[admin_xlsior]

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

[PaulDBrown · Accepted solution]

@admin_xlsior 

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!

[PaulDBrown]

@admin_xlsior 

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à!

[admin_xlsior]

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,