cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculated Column in Direct Query not working

Hello,

 

I'm new to this forum and Power BI, so please go easy on me 😁

 

I've been going nuts all afternoon, because a simple calculated column(as below) works in imported mode but not in direct query?  Can somebody explain why this isn't working and how I may be able to fix it?

 

CalSeasonStartDate =
CALCULATE(Min('Calendar'[Date]),
filter(all(bi_Grader_BatchV),bi_Grader_BatchV[TotalBinWeight] <> blank() && bi_Grader_BatchV[Season] = 'Calendar'[Season]))
 
I work in the fruit industry and each season has a start and end date determined by the first day records are entered and likewise for the last.  In my imported model, I used this column for other measures to tell me what the first day of packing was for each season(year).
 
Also, am I going to have continual issues with DQ vs import for similar dax expressions?  I want this data to be "polling" live so my understanding is that DQ is the way to go, but I need these expressions to work 😟
 
Thanks in advance
4 REPLIES 4
Super User IX
Super User IX

Re: Calculated Column in Direct Query not working

@CAB80 

Calculated column does not work in Direct Query mode. You have work in terms of measure

Try like


CalSeasonStartDate =
CALCULATE(Min('Calendar'[Date]),
filter(all(bi_Grader_BatchV), not(isblank(bi_Grader_BatchV[TotalBinWeight])) && bi_Grader_BatchV[Season] = max('Calendar'[Season])))

Refer how i done it with measure : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

You need create measure and take care of context



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Calculated Column in Direct Query not working

Thanks for the prompt reply, but this did not work.  It only gives me the start date across all seasons, not for reach season.See below.  So its grabbing the Min date, but not for each of the seasons.  For simplicity I only have two tables here, my calendar table and the bi_Grader_BatchV table which are lnked via date and packdate respectively.  My code worked perfectly as a column in the imported file.  The reason I wanted a column was so that I could easily reference any record to the start/end date of each season.

 

Any other help would be much appreciated.

Capture.PNG

Highlighted
Community Support
Community Support

Re: Calculated Column in Direct Query not working

Hi, @CAB80 

 

There are some limitation for calculated columns in DirectQuery mode. Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. For further information, please refer the official document .

 

As a workaround, you may try to create a measure. I created data to reproduce your scenario.

Testalq:

a1.png

 

Calendar:

 

Calendar = CALENDARAUTO()

 

 

There is a one-to-one relationship between two tables.

 

You may create a measure as below.

 

Result = 
var _season = 
IF(
    HASONEVALUE(Testalq[Season]),
    VALUES(Testalq[Season])
)
return
CALCULATE(
    MIN('Calendar'[Date]),
    FILTER(
        ALL(Testalq),
        Testalq[Season] = _season
    )
)

 

 

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Calculated Column in Direct Query not working

Thanks so much for your repl and for putting effort in to help me find a solution.  Unfortunately it has not solved my problem as yet though.  The output from your solution does appear to be what I want, but it didn't work for me.

 

Firstly, my tables are connected ([Pack Date] - > [Date]) with a one to many relationship where the Calendar table is the one.  Not sure if this impacts your solution.

 

Secondly, I would like the column/measure to be in the Calendar table.  This is so I can't use this as a quick and simple reference for many other measures.  It just makes sense to me.  I tried your solution in both and it didn't work.  Below is a pic of what happened when I put the measure in the other table(not the calendar table).  When I put it in the Calendar table the output was only the first date across all seasons.  So it didn't work either way. 

Capture.PNG

Put simply, what I really want here is for every record in my Calendar table, to show me the corresponding season start date/end date(separately) for that season(basically fiscal year).  This is why the calc column worked so well.

 

I'm really frustrated here because I felt I was really making progress with Power BI and now that I've switched to direct query, everything I learnt and applied is down the drain 😟

 

Anyway, any help you can offer is much appreciated.

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors