Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I'm in need of a helping hand with the following measure.
I need to calculate the status of an invoice according to two things - the last date selected by the user on the slicer and the day of payment for the invoice that is in my database. As such, if there is no date of payment for the invoice, or the date of payment is higher than the last date selected by the user, it has a status of 'Pendente', otherwise, it has the 'Compensado' status. I have a working version of it, displayed below, but it's too costly in terms of resources and cannot be displayed for the client in PowerBI. What can I do to optimize it?
StatusPagamentoCalculado:= var Data = LASTDATE(Calendario[Date]) var pend = IF (SELECTEDVALUE(FatoContasAReceber[DataCompensacao]) = BLANK(); "PENDENTE"; IF(SELECTEDVALUE(FatoContasAReceber[DataCompensacao]) <= Data;"COMPENSADO";"PENDENTE")) RETURN pend
does this measure return the expected results?
StatusPagamentoCalculado = VAR __DateSlicer = MAX ( Calendario[Date] ) VAR __DateInvoice = MAX ( FatoContasAReceber[DataCompensacao] ) VAR __Status = IF ( __DateInvoice = BLANK (), "PENDENTE", IF ( __DateInvoice <= __DateSlicer, "COMPENSADO", "PENDENTE" ) ) RETURN __Status
it should have better performance than the original one - the invoice date is evaluated once, and MAX should be faster than SELECTEDVALUE
Thanks for the reply.
It does works better, but it doesn't display correctly - I need the measure to be different for every invoice, and I can't use a calculated column due to the need for it to be dynamic. Even if otherwise, the measure still breaks when I have a larger amount of invoices to show.
Sorry for the double post, but I think I have discovered the problem - for each invoice, it calculates the measure for all rows of the Calendar table, which makes it have about 14 billion rows, breaking the memory limit.
I assume you don't display the measure for all dates/invoices, is that correct? Why do you think it iterates over the whole calendar table, do you use it later that way?
you could still add it as a temporary calculated column if it's needed for the calculations later, but in order to write tat I would need to understand what you're trying to achieve