Forum Discussion
Context problem. ALL, ALLSELECTED, ...
Well, I'm not English, either.
First of all, you should use Power Query to get your data into good shape. It's easy to use PQ and it's a very powerful data mash-up engine.
Secondly, you should read on DAX because you're completely in the dark on how it really works. If you do invest some time into it, you'll save yourself hours of frustration. I promise.
-- First of all, your Calendar must be marked as a Date Table -- in the model for this to work correctly. Secondly, you -- don't probably know this but it's of crucial importance -- that when you filter a measure by a whole table, you are in fact -- filtering by THE EXPANDED TABLE version of the table in question. -- You should never filter by a whole table unless you're asking for -- trouble or you know precisely why you do it. When you filter, -- please filter by individual columns only. You should also know -- that RELATED should only be used when there is a row context -- present, so it can be used in iterators only. CALCULATE -- is not an iterator. [Payable To Date] = var __maxDate = MAX( 'Calendar'[Date] ) var _payableToDate = CALCULATE ( SUM ( InvoicePayablePart[Debt] ), 'Calendar'[Date] <= __maxDate ) return __payableToDate [Paid To Date] = var __maxDate = MAX( 'Calendar'[Date] ) var __paidToDate = CALCULATE ( SUM ( InvoicePayedPart[Payed] ), -- change to Paid ALL( 'Calendar' ), InvoicePayedPart[PaymentDate] <= __maxDate ) return __paidToDate [Outstanding Debt] = [Payable To Date] - [Paid To Date]Best
Darek
Thank you Darek,
this example is not real, is a partial view from real model to help me to show the problem, and yes I've mistakes because I'm Spanish and seems i need to spend more time fixing my English ortography , :smileyvery-happy:
I know in a star schema you could've more benefits (performance and maybe measure and columns dax simplicity), but in this snowflake example the benefit is it's very close to database schema so you need less transformations (in production it hasn't hughe information), in the other hand i 've a strong curiosity to solve this problem and get the knowledgement.
Thank's for your considerations
PD. File ortography has been corrected
Well, I'm not English, either.
First of all, you should use Power Query to get your data into good shape. It's easy to use PQ and it's a very powerful data mash-up engine.
Secondly, you should read on DAX because you're completely in the dark on how it really works. If you do invest some time into it, you'll save yourself hours of frustration. I promise.
-- First of all, your Calendar must be marked as a Date Table
-- in the model for this to work correctly. Secondly, you
-- don't probably know this but it's of crucial importance
-- that when you filter a measure by a whole table, you are in fact
-- filtering by THE EXPANDED TABLE version of the table in question.
-- You should never filter by a whole table unless you're asking for
-- trouble or you know precisely why you do it. When you filter,
-- please filter by individual columns only. You should also know
-- that RELATED should only be used when there is a row context
-- present, so it can be used in iterators only. CALCULATE
-- is not an iterator.
[Payable To Date] =
var __maxDate = MAX( 'Calendar'[Date] )
var _payableToDate =
CALCULATE (
SUM ( InvoicePayablePart[Debt] ),
'Calendar'[Date] <= __maxDate
)
return
__payableToDate
[Paid To Date] =
var __maxDate = MAX( 'Calendar'[Date] )
var __paidToDate =
CALCULATE (
SUM ( InvoicePayedPart[Payed] ), -- change to Paid
ALL( 'Calendar' ),
InvoicePayedPart[PaymentDate] <= __maxDate
)
return
__paidToDate
[Outstanding Debt] = [Payable To Date] - [Paid To Date]Best
Darek
Yes Darek you are right and thank's for your response