topic Re: Offset function combined with field parameter in DAX Commands and Tips

Offset function combined with field parameter

Anonymous — Tue, 03 Oct 2023 12:52:25 GMT

Hello,

I would like to create a table that will show a change between 2 dates but requirement from the user is to be able to dynamically change time perspective view - so they would like to compare year to year / month to month / week to week / quarter to quarter / day over day. Since there is also week over week needed I cannot use built in time intelligence function. Till now I used additional table with dynamic date selection, however I thought that maybe it will be possible to write a metric with offset function and in the table itselt I could use field parameter. I created it but received info: Column [] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.
My metric is written as follows:

Total Change = VAR Offset = CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) ) VAR PoPChange = ([Total] - CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) )) / CALCULATE( [Total], OFFSET( -1, ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]), ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC) ) ) VAR Result = IF(NOT ISBLANK(Offset), PoPChange) Return Result

Dynamic date selection is a table created by using field parameter function:
|

Dynamic Date Selection = { ("calendar_date", NAMEOF('calendar'[calendar_date]), 0), ("week_end_date", NAMEOF('calendar'[week_end_date]), 1), ("month_end_date", NAMEOF('calendar'[month_end_date]), 2), ("year_no", NAMEOF('calendar'[year_no]), 3) }

Is it even possible to make changes in Change metric to be able to use it in table with field parameter in rows?

Re: Offset function combined with field parameter

lbendlin — Thu, 05 Oct 2023 00:13:08 GMT

This happens when you sort one column by another column (frequently done in Calendar tables). The sort column needs to be part of the calculation.

Might need to use a SWITCH statement to handle the different scenarios. It kinda defeats the purpose of the Field Parameters, but that's something you can recover from.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Offset function combined with field parameter

Anonymous — Thu, 05 Oct 2023 07:39:23 GMT

Here you can find powerbi file and excel with sample data.

https://www.dropbox.com/scl/fo/naasx1kq80ucpxw9o93oc/h?rlkey=mswsz1c7lgmwh1p98qh3t5fku&dl=0

Currently I have achieved what was wanted by creating additional dax table and using bookmarks.

However I just wanted to refactor this as I noticed that Field parameter option which I thought is more simple and more elegant.

Re: Offset function combined with field parameter

lbendlin — Fri, 06 Oct 2023 16:25:35 GMT

Your Calendar table has gaps

You also didn't specify the four digit years so Power BI got confused.

I'd recommend you fix that table first.