Forum Discussion
Dynamic Measure
Hi,
I'm creating a point in time report which requires a number of Dynamic components. I have the majority of them in the report but I seem to have fallen at the last hurdel. In the Screen shot below, I would like the last column 'TEST 0-30 days' measure to;
Sum "Balance" if "Age Category" = "0-30 days".
I'll break down each step below.
- Balance = a field from the data sourceDate_Selected
- Date_Selected = CALCULATE(MAX('Date_Table'[Date]),ALLSELECTED('Date_Table'))
- Doc_Age = ([Date_Selected] - MAX([Document.c2g__DueDate__c])) * 1
- Age Category =
IF([Doc_Age] < 1 ,"Current",
IF([Doc_Age] < 30, "0-30 days",
IF([Doc_Age] < 60, "31-60 days",
IF([Doc_Age] < 90, "61-90 days",
IF([Doc_Age] > 90, "90+ days", "Check")
)))) - TEST 0-30 days = SUMX('Transaction Line Item',
if ('Key Measures'[Age Category]="0-30 days",
CALCULATE(SUM('Transaction Line Item'[Balance])),
BLANK()))
So Point number 5 doesn't seem to work, I also tired the point below and this threw an error.
- Test 0-30 days = CALCULATE(SUM(Transaction Line Item'[Balance]),'Key Measures'[Age Category]="0-30 days")
From a model point of view the only tables relevent to these calculations are the DATE table and Transaction line item. these are linked by Date_Table[Date] to 'Transaction Line Item'[CreatedDate] on a 1:* relationship.
As always any help is greatly aprriciated.
Thanks
Dobby Libr3
SUMX(FILTER('Transaction Line Item','Key Measures'[Age Category]="0-30 days"),[Balance])
?
Hi Anonymous ,
for these kind of measures, deleting the connection between your date table and the fact table should help.
12 Replies
Hi Anonymous
please try the following:
CALCULATE(
SUMX(
VALUES('Transaction Line Item'[Trans Line Item]),
CALCULATE(SUM('Transaction Line Item'[Balance]))),
'Key Measures'[Age Category]="0-30 days")Hi ImkeF
I tried a Calculate column before and I got this error.
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
This same error appeared when I tired this expression.
Hi Anonymous
have you tried amitchandak 's proposal?
Otherwise: Could you please share a sample workbook?
Row context is very important for measure
TEST 0-30 days = SUMX(summarize('Transaction Line Item','Transaction Line Item'[DocId],"_sum"
if ('Key Measures'[Age Category]="0-30 days",
CALCULATE(SUM('Transaction Line Item'[Balance])),
BLANK()))[_sum])
Also, if possible filter on age in the formula, not on the age category
Anonymous I don't see Key Measures table in your model, what is that and how is it connected? Is Age Category a column or a measure? Is TEST 0-30 days supposed to be a measure or a calculated column? If calculated column what table?
TEST 0-30 days = SUMX('Transaction Line Item',
if ('Key Measures'[Age Category]="0-30 days",
CALCULATE(SUM('Transaction Line Item'[Balance])),
BLANK()))
