Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ffitzpatrick47
Helper II
Helper II

contexts in calculates

I have this function:

 

calculate([sumAmt],filter(all('public s_ar_f_compare_v'[cdate],'public s_ar_f_compare_v'[pdate]),isblank('public s_ar_f_compare_v'[cdate])&&'public s_ar_f_compare_v'[pdate]>=min('public s_ar_f_compare_v'[cdate])))

 

Which works, but I want to make sure that the filter context within the min function is unqualified.  I suspect it is filter context of the calculate function which is unqualified by [cdate] and [pdate], but is other wise qualified by its position on the pivot table.  Do I have to do something like:

calculate([sumAmt],filter(all('public s_ar_f_compare_v'[cdate],'public s_ar_f_compare_v'[pdate]),isblank('public s_ar_f_compare_v'[cdate])&&'public s_ar_f_compare_v'[pdate]>=min(all('public s_ar_f_compare_v'[cdate]))))

6 REPLIES 6
v-ljerr-msft
Employee
Employee

Hi @Ffitzpatrick47,



No, of course not, because even though the literature says all returns a table or a column unencumbered by a filter context, and min takes a column, the column all returns is not the column that min takes.


Yes, no need to change your original formula.

 

Do you still have any question on this issue? If not, could accept your reply above as solution to close this thread? Smiley Happy

 

Regards

Yes, I did have to change my original formula because it doesn't work. It had to be calculate(min(),filter(all(),finally the filter I want))

do we ever use filter a,d all outside of calculate. Calculate has to be the stupidest formula in programming. Look at how pandas handles this, folks. When you say you return a table or series, let the aggregate function work on that table or series.  It's utter nonsense. Min returns one number, but then in calculate, it doesn't return a number, it gets a new table pushed into it from filter as if its getting called by calculate rather than calculate taking the return value from min into its own calculation like every other excel function. This isn't exactly foreign, JavaScript and others can do this because functions are objects, but then they get treated like objects.  Alternatively, python uses decorators, and it's almost like calculate is a decorator. Of course these other languages use different syntax to differentiate the different behaviors, but when has msft done anything easy. One glance at c# and it was back to c++

Hi @Ffitzpatrick47,

 

I have also suffered a lot from DAX at the beginning. Smiley LOL However, after understanding how it works, I find that DAX could be powerful and easy to use. So I would suggest you read a book about DAX from start which could help you understand how it worksSmiley Happy

 

Regards

I've ready every book. If it wasn't for the fact that I can program in 5 other languages and build tools on Obiee, cognos, crystal and tableau, I'd think I was an idiot.  Note some of these tools require full time developers paid 150k+ to be used. The whole benefit of using power bi should be that a financial analyst or accountant can use it so they don't have to submit a ticket and wait for IT to write them a report.  With that piece of crap Dax language, that's notes single, but lets not talk in generalities, lets get really specific. 

 

Sumx(table....

li can't do this on a phone. I'll continue at the office. 

Ok, back...

Sumx(facttable,facttable[col] * related(dim[col]) <-- the computer should already know it's related.

calculate(sum(measure),filter(crossjoin(dima[c],dimb[c]),dima[c]=x&&dimb[c]=y))) <-- which in every other language would just be sum() where dima[c] = x && dimb[c] = y, because that relationship was already built.  By the way, you want to see the logic of how this is actually getting implemented if it were in a real language? calculate is really calling the sum function which has the measure curried into the function, except in every other language, that currying would be done before this call so that sum would be a first class object.  filter probably returns the lines that will be injected into the sum function by calculate.  There, they're using currying, partial state based functions and functions as a first class object all in a programming language that a financial analyst is supposed to understand.  I'll tell you where you can find those types of financial analysts. You find them on Wall Street, and they don't use power bi.  They use C and python

Or my favorite, pushing a parameter into a dimension of the model, which every other bi tool does naturally.  In excel, you have to materialize your dimension on a tab, and add the parameter right into that same table, then add the whole thing into the datamodel.

 

 

 

Ffitzpatrick47
Helper II
Helper II

No, of course not, because even though the literature says all returns a table or a column unencumbered by a filter context, and min takes a column, the column all returns is not the column that min takes.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.