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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeanetted
Frequent Visitor

CALCULATE WITH TWO FILTERS

Hi!!

I have a ledger table and I want to calculate the sum of all the transactions. I used CALCULATE, filtering by ALL Calendari because I want the acumulated value.

Valor immoble =
VAR maxF = MAX(Calendari[Date])
RETURN
CALCULATE(SUM(LEDGERTRANS_Immobles[Import]), FILTER(ALL(Calendari), Calendari[Date] <= maxF))
 
But also, I need filter my ledger table because I don't want to sum the registers that the sell-date is less than the selected date:
Valor immoble net =
VAR maxF = MAX(Calendari[Date])
RETURN

CALCULATE(SUM(LEDGERTRANS_Immobles[Import]), FILTER(ALL(Calendari), Calendari[Date] <= maxF), FILTER(LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Data_Venda]>maxF))
 
My problem is that with Valor Immoble measure I obtain the sum of all transactions (including the selled features). If I use the measure Valor immoble net, I only obtain the sum of the transactions in the current year.
 
How can I mix them?Captura.JPG

 

 
1 ACCEPTED SOLUTION

Hi @Jeanetted  - sorry I wasn't able to get to this until now.  Can you confirm that the following results are what is expected?

 

Year = 2020

2021-03-01 09_01_07-Immobles_forum - Power BI Desktop.png

Year = 2019

2021-03-01 09_01_23-Immobles_forum - Power BI Desktop.png

Year = 2018

2021-03-01 09_01_40-Immobles_forum - Power BI Desktop.png

Year = 2017

2021-03-01 09_02_35-Immobles_forum - Power BI Desktop.png

Year = 2016

2021-03-01 09_02_51-Immobles_forum - Power BI Desktop.png

If it is, here is the code for you to use

 

Valor immoble net = 
VAR maxF =
    MAX ( Calendari[Date] )
RETURN
    CALCULATE (
        SUM ( LEDGERTRANS_Immobles[Import] ),
        FILTER ( 
            ALLEXCEPT( LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Promoció] ),
            LEDGERTRANS_Immobles[Data_Venda] > maxF ),
        FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
    )

 

David

View solution in original post

14 REPLIES 14
dedelman_clng
Community Champion
Community Champion

Hi @Jeanetted - looks like you need to remove the filters from LEDGERTRANS_Immobles to counteract the slicer. You can use ALL for that just like with the Calendari table.

 

Valor immoble net =
VAR maxF =
    MAX ( Calendari[Date] )
RETURN
    CALCULATE (
        SUM ( LEDGERTRANS_Immobles[Import] ),
        FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF ),
        FILTER ( ALL ( LEDGERTRANS_Immobles ), LEDGERTRANS_Immobles[Data_Venda] > maxF )
    )

Hope this helps

David

Thanks David, but the solution don't work. The relationship between LEDGERTRANS_Immobles and Calendari is another field:

 

Calendari[Date]----------LEDGERTRANS_Immobles[Data]

 

I need use the secondary relashionship:

Calendari[Date]----------LEDGERTRANS_Immobles[Data_Venda]

But I don't know hao to use the function (USERELATIONSHIP) into a FILTER Function.

 

 

 

(Data, not Data_Venda). I think I need use the secondary relationship 

 

Hi @Jeanetted -

 

USERELATIONSHIP is a CALCULATE modifier so you're really close.  The code should look something like

 

Valor immoble net =
VAR maxF =
    MAX ( Calendari[Date] )
RETURN
    CALCULATE (
        SUM ( LEDGERTRANS_Immobles[Import] ),
        FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF ),
        USERELATIONSHIP ( LEDGERTRANS_Immobles[Data_Venda], Calendari[Date] )
    )

 

This assumes that you currently have the relationship in your model, just deactivated.

 

If that still doesn't work, can you share a copy of your report file with sensitive data removed?

 

David

It was a good idea, but also it does'nt work.

 

I'll show you the data.

I want sum the field Import acumulated since a selected data from a calendar, but only id Data_Venda > SELECTED date.

In this example, I don't want to sum the registers from LOCAL CAMBRILS. 

I need filter the Calendari Table because I need to sum the acumulated registers of the field Import.

And I need filter the table show below (LEDGERTRANS_immobles) if Data_Venda > Selected Data.

But the relationship between Calendari and LEDGERTRANS_immobles is the field Data, not Data_Venda.

 

Let me see if I understand the scenario.

 

You need the sum of a value on LEDGERTRANS_immobles where Date is less than the max date, but Date_Venda is greater than the max date?

 

Please confirm that this is the scenario (your pictures didn't come through if there were any in your last post).

Yes, is this!!

Captura2.JPG

I'm suprised that my first set of code doesn't work. Maybe try this (which is just reversing the filters, which shouldn't make a difference but you never know).

 

Valor immoble net =
VAR maxF =
    MAX ( Calendari[Date] )
RETURN
    CALCULATE (
        SUM ( LEDGERTRANS_Immobles[Import] ),
        FILTER ( ALL ( LEDGERTRANS_Immobles ), LEDGERTRANS_Immobles[Data_Venda] > maxF ),
        FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
    )

If that doesn't work, can you provide a copy of your report with sensitive data removed? There may be other filters at play that are preventing you from getting the answers you want.

 Hello dedelman,

 

I prepared a little copy. How can I send you?

 

Share it as a link via OneDrive, Dropbox, etc 

I cannot access that link since I am not in your organization

Hi @Jeanetted  - sorry I wasn't able to get to this until now.  Can you confirm that the following results are what is expected?

 

Year = 2020

2021-03-01 09_01_07-Immobles_forum - Power BI Desktop.png

Year = 2019

2021-03-01 09_01_23-Immobles_forum - Power BI Desktop.png

Year = 2018

2021-03-01 09_01_40-Immobles_forum - Power BI Desktop.png

Year = 2017

2021-03-01 09_02_35-Immobles_forum - Power BI Desktop.png

Year = 2016

2021-03-01 09_02_51-Immobles_forum - Power BI Desktop.png

If it is, here is the code for you to use

 

Valor immoble net = 
VAR maxF =
    MAX ( Calendari[Date] )
RETURN
    CALCULATE (
        SUM ( LEDGERTRANS_Immobles[Import] ),
        FILTER ( 
            ALLEXCEPT( LEDGERTRANS_Immobles, LEDGERTRANS_Immobles[Promoció] ),
            LEDGERTRANS_Immobles[Data_Venda] > maxF ),
        FILTER ( ALL ( Calendari ), Calendari[Date] <= maxF )
    )

 

David

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.