Forum Discussion
CALCULATE with Filter not working
- 4 years ago
Hi Draszor
Here is your sample file with the solution https://www.dropbox.com/t/MEcfn1F5U6wFM3aO
I have created "Inactive" relatioships between date tables and the fact table.
With this we can just activate the required relationship only when needed.
The code is little long but pretty simple. Just need to check when fact table is empty on the check conditions (at the selected date) if at either D1 or D2 the table has no raws then the formula retruns blank otherwise the difference calculation is curried out.
Same fore amount calculation, we calculate the sum by activating the the relationship with the designated date table and filter the results fro only Actual Costs.Actual Cost Difference = VAR AmountD1Actual = CALCULATE ( SUM ( Sheet1[Amount] ), USERELATIONSHIP ( Sheet1[Date], Date1[DATE1] ), Sheet1[Amount Type] = "Actual Costs" ) VAR AmountD2Actual = CALCULATE ( SUM ( Sheet1[Amount] ), USERELATIONSHIP ( Sheet1[Date], Date2[DATE2] ), Sheet1[Amount Type] = "Actual Costs" ) VAR CheckAmountD1 = CALCULATE ( COUNTROWS ( Sheet1 ), USERELATIONSHIP ( Sheet1[Date], Date1[DATE1] ), ALLEXCEPT ( Sheet1, Sheet1[Project], Sheet1[Date] ) ) VAR CheckAmountD2 = CALCULATE ( COUNTROWS ( Sheet1 ), USERELATIONSHIP ( Sheet1[Date], Date2[DATE2] ), ALLEXCEPT ( Sheet1, Sheet1[Project], Sheet1[Date] ) ) VAR Result = IF ( OR ( ISBLANK ( CheckAmountD1 ), ISBLANK ( CheckAmountD2 ) ), BLANK (), AmountD2Actual - AmountD1Actual ) RETURN ResultBy the way, using relationships deliveres the best performance among all other options.
You report shall look like this
Please let me know if this answeres your query. Have a great day!
Draszor absolutely right.
1. I think I can do something regarding totals and subtotals. I'll update later and send to you.
2. This is not related to this specific measure. Any measure which is built on such specific requirement will have the same issue. You specifically asked for that. Here I quote "3. if AMOUNT_D1 and AMOUNT_D2 is not null for the Project ( meaning, project is shown in DIFF TABLE) then I would like to see all diffferences for WBS and WBS1, even if AMOUNT_D1 or AMOUNT_D2 for given WBS or WBS1 are null. " end of quote. In this case you have two options: either to have a separate measure for each case or to utilize calculation groups.
thank you tamerj1 in advance for your input. good to have an opportunity of learning some insights on my real business problem.
Regarding point no 2 - you are right. I was too general in my description. The reason was that the formula I was using for Amount diff (with Calculate( ..., filter,,,)) was working as well when projects were not present in the matrix. that way I was able to build few matrixes that were keeping the described logic, but were distributing the amounts per other dimensions than projects.