Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Draszor
Helper III
Helper III

CALCULATE with Filter not working

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.

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.
2.png
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
1.png
Please let me know if this answeres your query. Have a great day!

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

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.
2.png
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
1.png
Please let me know if this answeres your query. Have a great day!

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)

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

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?

 

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

 

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

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

thank you @tamerj1 

v-xiaotang
Community Support
Community Support

Hi @Draszor 

Please check the picture below,

vxiaotang_0-1645517407234.png

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.

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

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

Draszor
Helper III
Helper III

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)

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.