topic Re: Getting unexpected exception with measure in DAX Commands and Tips

Getting unexpected exception with measure

Jean_Ferro — Mon, 17 Jun 2024 13:31:28 GMT

Hello everyone!

I am creating this measure that works as needed only if I keep the date fixed like DATE(2024, 6, 10), if I try to make the date dynamic using a measure that returns the greatest date chosen by the user in the calendar like this:

maxx(dCalendar,[Date])

I get the error "unexpected exception"

SUMX(
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fStock,
fStock[DTABASE] <= DATE(2024, 6, 10)
),
fStock[PRODUCT_CODE],
fStock[BRANCH],
"MaxDate", MAX(fStock[DTABASE])
),
"Cost",
CALCULATE(
MAX(fStock[COST]),
FILTER(
fStock,
fStock[DTABASE] = [MaxDtaEntradaSaida] &&
fStock[PRODUCT_CODE] = EARLIER(fStock[PRODUCT_CODE]) &&
fStock[BRANCH] = EARLIER(fStock[BRANCH])
)
)
),
[Cost]
)

Re: Getting unexpected exception with measure

Greg_Deckler — Mon, 17 Jun 2024 13:34:42 GMT

Jean_Ferro What about just MAX('dCalendar'[Date])

or:

Measure = VAR __Date = MAX('dCalendar'[Date]) VAR __Result = SUMX( ADDCOLUMNS( SUMMARIZE( FILTER( fStock, fStock[DTABASE] <= __Date ), fStock[PRODUCT_CODE], fStock[BRANCH], "MaxDate", MAX(fStock[DTABASE]) ), "Cost", CALCULATE( MAX(fStock[COST]), FILTER( fStock, fStock[DTABASE] = [MaxDtaEntradaSaida] && fStock[PRODUCT_CODE] = EARLIER(fStock[PRODUCT_CODE]) && fStock[BRANCH] = EARLIER(fStock[BRANCH]) ) ) ), [Cost] ) RETURN __Result

Re: Getting unexpected exception with measure

Jean_Ferro — Mon, 17 Jun 2024 14:10:57 GMT

Unfortunately the same unexpected exception error, if I change it to DATE(2024,6,5) for example it works, but I need it to be dynamic.

The measure to return the greatest date seems correct to me:

GreatestDataStock = maxx(dCalendar,[Date])

Re: Getting unexpected exception with measure

Greg_Deckler — Mon, 17 Jun 2024 14:12:41 GMT

Jean_Ferro Any chance you can post a link to the file? Otherwise it's really hard to understand what is going on here.

Re: Getting unexpected exception with measure

Jean_Ferro — Mon, 17 Jun 2024 14:53:16 GMT

I was preparing a file to upload with just the information to reproduce the error but when I tested it on this test file it worked.

In my production development I use two databases communicating by direct query sharing 1 calendar, when I separated the calendars leaving one for each database it worked.

I'm going to use it like this for now, but I would like to find a way to use a calendar for both databases without the error.

Re: Getting unexpected exception with measure

Greg_Deckler — Mon, 17 Jun 2024 15:11:54 GMT

Jean_Ferro You may want to play around with privacy level settings then. Depending on those settings, some cross query/source queries aren't allowed.

Re: Getting unexpected exception with measure

Jean_Ferro — Mon, 17 Jun 2024 16:40:56 GMT

With this method, i change privacy level for this on all file involved:

Is here?

Still dosent work.

Only works on this way:

Re: Getting unexpected exception with measure

Greg_Deckler — Mon, 17 Jun 2024 16:58:15 GMT

Jean_Ferro Yep, that's what I was referring to. Interesting, let me do some research as maybe there is some kind of formula firewall thing going on here.

Re: Getting unexpected exception with measure

Jean_Ferro — Mon, 17 Jun 2024 17:11:08 GMT

I tried this way too and it still doesn't work.

I really appreciate your attention and help, I will continue working in the way it is working, if you have any more ideas I would be very happy if you shared them!