CALCULATE with Filter not working

[Draszor]

Hi,

I attach the LINK to the example file where I recreated the problem.

I created the table (DIFF TABLE) that should show:

1. difference between the amounts from 2 chosen dates

2. only for those projects that total amounts (for the project) are not null for chosen dates (that is why P4, P5, P6, P7 are not shown, having null for AMOUNT_D1)

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. 

Point 3 is my problem. please have a look into P1 (project) - Commissioning (WBS). it is null for AMOUNT_D1, and it is not shown in DIFF TABLE. the same with P1 (Project) - Instal (WBS) - engineering (WBS1) . this is missing as well but I want to see this combination in DIFF TABLE.

I don't want to see only the projects with total amunt (D1 or D2) being null, somehow my Calculate formula uses this validatio to all chosen sub-dimensions (WBS and WBS1) as well. I do not want this.

[tamerj1 · Accepted solution]

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
    Result

By 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]

Dear @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)

[tamerj1]

@Draszor You are right, using AND will show more results. Actually I may understood your requirement, I thought you need to blank the result if either of them is blank.

[Draszor]

Hi, 

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]

@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. 

[tamerj1]

@Draszor Can you clarify exactly what is required to show in totals and subtotal at all levels including grand total? Thank you

[Draszor]

Yes. I made such clarification in attached ( LINK ) Projects excel file.

thank you @tamerj1 

[v-xiaotang]

Hi @Draszor 

Please check the picture below,

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Draszor]

hi, 

Yes exactly. the filter for blank amounts should be applied ONLY to projects. if for total project the Amount_D1 AND Amount_D2 is not blank, then I would like to see all sub-dimensions (WBS, WBS1) even those for which either of those amounts is blank

[tamerj1]

Hi @Draszor 
Did you have the chance to check my solution?

[Draszor]

Hi All,

what I did in Actual Cost Diff measure - I was trying to build my condition on the table containing ONLY projects (in the filter() function). I remember that PBI in the filter context interprets filters as tables, so I thought that building a table that does not contain dimensions splitting project into details, but contains ONLY projects, the filter context will be working ONLY on the project level. It does not!

not sure what I misunderstand...

thank you for your help. I was testing other solutions, but I was not able to implement the following:

• filter out Projects that do not fulfil the criteria, and then show all the rest (even those WBS's and WBS1 where the condition is fulfilled, but for the project the WBS or WBS1 belongs to it is not fulfiled)