topic Re: Quite frustrated - need help in DAX Commands and Tips

Quite frustrated - need help

AviBI — Tue, 04 Aug 2020 09:53:41 GMT

Hi all

Trying to filter to Last week's items as follows:

Period Lookup Table Filters Items Data Table - One to Many

LastWkItems = Calculate ([TotalItems] , Period[WeekofYr] =31 , Period[Year] = 2020 )

Obviously, I do not want to keep hardcoded values the week number and year (31 & 2020 in this case) to avoid manual intervention every week. So I defined a couple of variable in the Period Lookup Table as follows:

LastDate = Lastdate (Period[Date])

LastWk = WeekNum (LastDate)

Now when I used the variable in the above formula to replace the hardcoded 31, I get the following error:

"A function Calculate has been used a True/False expression that is used as a table filter expression. This is not allowed"

Pulling my hair out as to why this is not working when essentially I am replacing a hard number with a variable

Appreciate the help guys

amitchandak , Greg_Deckler parry2k Anonymous

Re: Quite frustrated - need help

amitchandak — Tue, 04 Aug 2020 09:58:36 GMT

AviBI , see if these can help

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

else

Can you share sample data and sample output in table format?

Re: Quite frustrated - need help

Anonymous — Tue, 04 Aug 2020 10:26:28 GMT

You are not showing us everything, AviBI. What do you mean by "I defined a couple of variables in the Period Lookup Table"? This makes little sense. You can create variables in a formula, not in a table. Secondly, you can use variables in your measure formula instead of the hard-coded values in exactly the same places but the variables MUST be defined in the measure itself, not outside. Remember that the syntax you're using is syntax sugar since all conditions under CALCULATE must be tables. So, this:

calculate( [measure], T[Col] = a )

is always translated into:

calculate( [measure], filter( all( T[Col] ), T[Col] = a ) )

"a" must be a value, can't be a measure, for instance.

Re: Quite frustrated - need help

AviBI — Tue, 04 Aug 2020 11:08:36 GMT

This is the end result I am after - but I have acheived this using manual filters and I do not want to update the filters every week. This should be automated.

Common field between Calendar & Data (Items Table) is "Period"

Measures:

TotalItems = Calculate (Sum ('Items&Serv'[Items]), 'Items&Serv'[ItemType] = "Items")

ItemType column comprises 5 different categories but I am only interested in "Items" and hence I have used the measure to isolate the total for these

I am only interested in last weeks Items (week 31 of the year). The week numbers are available in the Calendar as a custom power query custom column

So I defined two measures:

LastDate = Lastdate (Calendar[Date]) - this pulls the last date from the data table (Calendar table references the Items Table) in Power Query

LastWk = WeekNUM (LastDate) - This takes the LastDate measure above and calculates the Dax WeekNum

So far so good

This is exactly what I need.

Now in order to get to Last weeks items through a measure approach (not manual filters), this is how I think the measure should look like:

LastWeekItems = Calculate (TotalItems , WeekofYr = LastWk , Year = 2020)

This does not work. "Calculate" can not be used with True/False error

This however works:

LastWeekItems = Calculate (TotalItems, WeekofYr = 31, Year = 2020)

Now you may tell me that I am going about it all wrong which is fine. I just need the report to pickup the last week, and display the items on a daily basis (as shown above) and I need this to happen automatically based on the data and relationships (as above)

Hope this gives all the info you required to help

amitchandak Anonymous Greg_Deckler

Re: Quite frustrated - need help

Anonymous — Tue, 04 Aug 2020 11:11:09 GMT

// Of course the one with hard-coded values works. // I have already explained it in my first post. // If you read it carefully, you'd know what to do... [LastWeekItems] = var __lastWeek = [LastWk] return Calculate ( [TotalItems], WeekofYr = __lastWeek, Year = 2020 // this should not be hard-coded )

Re: Quite frustrated - need help

AviBI — Tue, 04 Aug 2020 11:15:07 GMT

I did read your last post and did not quite understand as I am only used to defining straight forward measures (don't even know how to use "var" etc.)

Also understand that 2020 needs to be treated in a similar way as the week numbers. so I'll need to follow the same approach for the year as well

Thanks a lot for your help. I will now try this