Help with DAX on DirectQuery

[parry2k]

Hi there,

Need help with following scenario, I'm using Azure SQL Server with DirectQuery in PowerBI.

I have phone calls data table which is related to calendar table. Problem I'm trying to solve here is to compare number of calls on a date with average number of calls in previous days.

Here is what average mean:

I have a slicer table, allows you to select average for how many days? 7, 15, 30, 45, 60 

I have a formula called "How many Average Days?" = if(HASONEVALUE(SlicerTable), VALUES(SlicerTable[Days]), 7), giving default value to 7days, if no value is selected in formula

So for example if we are looking at data as of March 03rd, so average will be number of calls between start date and yesterday, in this case start date will be Feb 24th (Yesterday - How many average days), in other words (March 02nd  - 7 Days) and end date will be yesterday (March 02nd).

We will count all the calls between Feb 24th and March 02nd and then divide by How many average days? 

In this whole thing I'm unable figure out how to get start date since DAX doesn't allows DATEADD functionality on directquery and I'm unable to figure out any other way. I hope it is detailed enough to provide help. 

Thanks,

Parv

lls in a date range / number of days

[Greg_Deckler]

Does the old ([date] + 7 * 1.) trick work? That would add 7 days to [date] for example.

[parry2k]

I get this error when I'm trying to add following column in Calendar Table

Here is how "How Many Average Date?" measure is calculated in calendar table:

How many average days? = IF(HASONEVALUE('Days for Average'[Days]), MAX('Days for Average'[Days Value]), 30) 

[v-qiuyu-msft]

Hi @parry2k,

From the error message, it indicates CountRows() cann't be used to create a calculated column in DirectQuery. Please check if 'Days for Average'[Days] or 'Days for Average'[Days Value] is calculated from the CountRows() function.

Depends on results you want to display in the visual, you can try to change the calculated column to a measure, as CountRows is available in measure.

Best Regards,

Qiuyun Yu

[parry2k]

Hi @v-qiuyu-msft

As mentioned in my original post, my not using countrows anywhere and that was surprising to me as well when I get this countrows error message, let me know what other information I can provide. 

[parry2k]

Also if i make following as a column in my calendar table and use this column in my calculation, all works well.

How many Average Days?" = if(HASONEVALUE(SlicerTable), VALUES(SlicerTable[Days]), 7)

changed to column as fixed value

How many average days? = 30

[parry2k]

Any help on this or alternate solution? Thanks!