cancel
Showing results for 
Search instead for 
Did you mean: 
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




Proud to be a Super User!




View solution in original post

14 REPLIES 14
dedelman_clng
Super User II
Super User II

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




Proud to be a Super User!




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




Proud to be a Super User!




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




Proud to be a Super User!




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.




Proud to be a Super User!




 Hello dedelman,

 

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

 

Share it as a link via OneDrive, Dropbox, etc 




Proud to be a Super User!




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




Proud to be a Super User!




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




Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors