Sum with filters in different tables - direct query mode

[Anonymous]

Dear all,

I've created a report in PBI Desktop that pulls data in direct query mode from our Dynamics CRM database (on premises, direct SQL query). I want to visualize the opportunity pipeline over the last (and upcoming) months.

I use two tables, the "opportunities" and a "timetable" which contains every day of the last an upcoming years witth the date.

I want to visualize the open opportunites and their summarized estimated revenue values at any specific day in the last year. 

The filter is, in plain words: summarize the revenue of any opportunity where the actual close date is "smaller" than the date in the time table or the actual close date contains no value AND the creationdate of the opportunity is before the date in the timetable.

I've tested this scenario in "import mode" and it works fine when I create a column in the time table with the following DAX

********************************

pipeline = sumx(FILTER(opportunity;
                 AND
                       (OR
                            (IF(opportunity[actualclosedate] > timetable[date];TRUE();FALSE());
                                 ISBLANK(VKC[actualclosedate]));
                       (opportunity[createdon] < timetable[date])));
                opportunity[estimated revenue])

********************************

Unfortunatley, it is not allowed to created a column in direct query using SUM function.

The described DAX does not work within a measure.

Any suggestions?

Thanks.

Stefan

[v-danhe-msft]

Hi @Anonymous,

From your formula, it seemed that you have used the data in 'VKC' table, should it be opportunity?

In direct query mode, you could just create a measure and may be you could try to modify your formula as below:

pipeline = sumx(FILTER(opportunity,
                 AND
                       (OR
                            (IF(opportunity[actualclosedate] > MAX(timetable[Date]),TRUE(),FALSE()),
                                 ISBLANK('opportunity'[actualclosedate])),
                       (opportunity[createdon] < MAX(timetable[Date])))),
                opportunity[estimated revenue])

If it still could not work, could you please share some sample data to have a test?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

first of all, thank you.

Your are right, "VKC" should be "opportunity".

The measure formula doesn't work, the shown error is (translated):

"Function "Filter" is not supported in this context in direct query mode"

Here you'll find some sample data.

Thanks again.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, it could work on my side to use the "FILTER" function.

Could you have checked the "Allow unrestricted measures in DirectQuery mode" by selecting File > Options and settings > Options?

Regards,

Daniel He 

[Anonymous]

Hi @v-danhe-msft,

thank you for your work.

I'm sorry, but I do not have this option to check.

I'm using the latest update (October) with german localisation.

Thank you

Stefan

[Anonymous · Accepted solution]

Hello,

just to close the Thread, I switched to Mixed Mode (Preview Features) and added a new Column in an "Import" Dataset.

It works with the above mentioned DAX Formula.

Not the exact way I wanted, but ok for the moment.

Thanx for the help