Forum Discussion
Context problem. ALL, ALLSELECTED, ...
Hi,
I need help with context,
I 've this schema
schema
The Invoice's Total is saved in InvoicePayablePart (summatory) because InvoicePayablePart has the parts in wich the Invoice is partitioned based on Expiration Date, each part of these Expirations is also partitioned when payments are done (InvoicePayedPart).
I 've this measure:
Debt =
CALCULATE(
SUM(InvoicePayablePart[Debt]) ;
FILTER(All(InvoicePayablePart);
Related(Invoice[Date])<=max(Calendar[Date])
)
) -
CALCULATE(
SUM(InvoicePayedPart[Payed]) ;
FILTER(All(InvoicePayedPart);
InvoicePayedPart[PaymentDate]<=max(Calendar[Date])
)
)and get this chartI attach the example
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
4 Replies
Personally, I think you should change the model. The one you've got right now is not the easiest to work with and the DAX measures will not only be overly complex but they will also be slow. But it's your call. You'll pay the price later on.
The design does violate many rules of good dimensional design. I'd re-think it and try to make it into a STAR SCHEMA (maybe with some flakes). You could probably remove the Invoice table completely and morph it into InvoicePayabePart (changing the name of the latter as well). Then you'd have another fact table connected to dimensions only (you should never, ever join 2 fact tables to each other) which would store payments. Clean design will get you not only a simpler model but will also let you write simple AND CORRECT DAX. Think about the benefits...
Secondly, please correct mistakes in the names of entities. Do you really have a "costumer" or rather a "customer"? Unless you're working in the puppet industry and know sb who's a costumer... :) Also, is "payed" a real word? Or you meant "paid"?
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 correctedWell, 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
