Forum Discussion
Helper IIICALCULATE with Filter not working
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!
Helper IIIDear tamerj1
elegant usage of ALLEXCEPT(), the function I knew but had never used in such context = I did not know it good enough. Thank you.
last queston. I changed the formula in "result" variable from OR to AND - please notice that even though the per project results did change (I see more projects now and this is correct), the total result did not move. re-calculating in excel, the total (3,094) is shown for AND conditin, even in the Actual Cost Difference measure (based on OR).
LINK (file name PROJECTS_solution)
Helper IIIHi,
I spoted two (hopefully small problems) with the approach tamerj1 proposed:
1. totals - they somehow are insensitive for the formula used and do show total-total difference (with no limitations embedded in the measure logic)
2. using different dimensions (for instance - instead of projects, I would like to use a country the project was realized in and show the difference between 2 periods for nonblank projects), the measure gives very different (not correct) distribution of the differences.
I would guess this is a problem with "if" statement that works if the filter context has project in it, but does not work when project is missing... does my observation make sense?
- tamerj1
Community Champion
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.- Draszor
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.