Calculate measure filter different table

DAX DAX Calculations
1

Hello you all
I’m trying to build a measure that gives values from another table.
The measure [Net] is a sum (net) from another table. So, this one is the normal measure:

Measure 1= CALCULATE(
[Net],
FILTER( ‘Tab Mov’, ‘Tab Mov’[Conta DR] = “Fornecimentos e Serviços Externos” ))

I would like to do the same measure but from another table which already is related. Something like:

Measure 2 = CALCULATE(
[Net],
FILTER( ‘Tab SNC’, ‘Tab SNC’[Conta DR] = “Fornecimentos e Serviços Externos” ))

I’d like to know if there’s another way of doing this measure.

Thanks a lot
Pedro

2

Hello @pedroccamara,

Thank You for posting your query onto the Forum.

So basically, you want to avoid writing the text manually which is mentioned under the quotes, is that it? If yes, in my file I’ve written the formula as follows to avoid writing the text manually. Below is the formula and screenshot provided for the reference -

Totals Sales Lookup = 
VAR SelectedProducts = SELECTEDVALUE( Sales[Product Description Index] )

VAR Results = 
CALCULATE( [Total Sales] ,
    FILTER( Sales , 
        Sales[Product Description Index] = SelectedProducts ) )

RETURN
Results

And then to have the “Grand Totals” at the bottom -

Total Sales Lookup (Totals) = 
SUMX(
    SUMMARIZE(
        Products , 
        Products[Product Name] , 
        "Totals" , 
        [Total Sales Lookup] ) , 
    [Totals] 
)

image
image589×709 24.9 KB

In my file, the Sales (Fact Table) and the Products (Dimension Table) table is linked/related with the “Product Description Index” which is a common field. Similarly, in your file, you can follow the same pattern and get the results.

I’m also attaching the working of the PBIX file for the reference so that you can view how the formula is working.

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Calculate Measure Filter From Different Table - Harsh.pbix (661.1 KB)

1 Like
3

Hello my friend,
Thank you very much for this solution.
Best regards
Pedro

4

Hello @pedroccamara,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help and you found this solution useful. :+1:

Thanks and Warm Regards,
Harsh