topic Re: DAX Code not working in PBI Desktop in DAX Commands and Tips

DAX Code not working in PBI Desktop

Gerald23 — Fri, 09 Sep 2022 12:01:44 GMT

Hi Everyone,

I would like to add 5 working days to a date I already have in my Sales table. To achieve this i have followed the steps described in this video: https://www.youtube.com/watch?v=2HkBbqxBzF0

He describes 2 options the first one works but he explains that the second is better performance wise. So I followed the second one and it gives me the code below which works great in the Dax Studio but when I move the code to the Power BI Desktop file it doesnt.

So how should I change this code so that it works in a new calculated column in the Power bi Desktop?

DEFINE COLUMN 'Date'[WDN] = VAR WorkingDates = CALCULATETABLE( Values ( 'Date'[Date]), REMOVEFILTERS ( 'Date'), 'Date'[Working Day] = TRUE ) VAR Result = RANKX ( WorkingDates, 'Date'[Date], , ASC ) - NOT ('Date'[Working Day]) RETURN Result COLUMN 'Sales'[WDN+5] = VAR CurrentWDN = RELATED( 'Date'[WDN] ) VAR CurrentPlus5 = CurrentWDN + 5 VAR Result = LOOKUPVALUE( 'Date'[Date], 'Date'[WDN], CurrentPlus5, 'Date'[Working Day], TRUE ) RETURN Result EVALUATE ALL ( 'Sales'[Period.Date Value], 'Sales'[WDN+5]) ORDER BY 'Sales'[Period.Date Value]

Re: DAX Code not working in PBI Desktop

daXtreme — Fri, 09 Sep 2022 12:24:29 GMT

This is a QUERY, not something you can put in PBI just like that. If you want to create a column in a table, then you should extract the pieces that can be used in a calculated column. Certainly, you CANNOT DEFINE anything in a calc column.

Re: DAX Code not working in PBI Desktop

Gerald23 — Wed, 14 Sep 2022 13:21:11 GMT

So I was able to solve this by splitting the code so that it would make two new calculated columns instead of just one.

First i created a column called WDN using the code below

WDN = VAR WorkingDates = CALCULATETABLE( Values ( 'Date'[Date]), REMOVEFILTERS ( 'Date'), 'Date'[Working Day] = TRUE ) VAR Result = RANKX ( WorkingDates, 'Date'[Date], , ASC ) - NOT ('Date'[Working Day]) RETURN Result

Then i added another calculated column called WDN+5 using this code

WDN+5 = VAR CurrentWDN = 'Date'[WDN] VAR CurrentPlus5 = CurrentWDN + 5 VAR Result = LOOKUPVALUE( 'Date'[Date], 'Date'[WDN], CurrentPlus5, 'Date'[Working Day], TRUE ) RETURN Result

Now I can use this field in filtering to get the results that are needed for my report.