DAX Calculate and filter on Related Table but Different Column

[asjones]

I am trying to do a CALCULATE with a filter based on a related table.

I have tables and relatins like like
Fact Table[Items] <many-- 1> Dim Table[Items]

However I wan to do a DAX CALCULATE like this
CALCULATE( SUM(Fact Table[amount]) , Dim Table[Color] = "Green")

Not sure if there is an easy or "right" way to do this but it would make things easer in my data model.

Any ideas or suggestions?

thanks

Alan

[AlB]

@asjones 

Are there other filters being applied? Can you share the pbix?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[asjones]

Still no luck... modified my model to see how one item works and one does not work.

This Dax Works
where Financal Actuals has column head account related to Head Account in the Head Account Table.
Expense 1 =
VAR _Amount =
CALCULATE (
SUM ( 'Financial Actuals Table'[Amount4] ),
KEEPFILTERS (
VALUE ( 'Head Account Relations'[Head Account] ) >= 50000
&& VALUE ( 'Head Account Relations'[Head Account] ) <= 79999
&& VALUE ( 'Head Account Relations'[Head Account] ) <> 56950
),
KEEPFILTERS (
VALUE ( 'Financial Actuals Table'[Account] ) <> 96910
&& VALUE ( 'Financial Actuals Table'[Account] ) <> 96950
) ,
KEEPFILTERS( VALUE('Main Project Master File - PMF'[PMF Project Type] ) >= 30 &&
VALUE( 'Main Project Master File - PMF'[PMF Project Type] ) <= 69
)
)
RETURN
_Amount

This DAX does not work
Where is use a different 1-Many relation between Account Relation table and Finacial Relation Table.
the results here produce nothing (not even zero).
Expense 2 =
VAR _Amount =
CALCULATE (
SUM ( 'Financial Actuals Table'[Amount4] ),
KEEPFILTERS (
VALUE ( 'Account Relations'[Head Account] ) >= 50000
&& VALUE ( 'Account Relations'[Head Account] ) <= 79999
&& VALUE ( 'Account Relations'[Head Account] ) <> 56950
),
KEEPFILTERS (
VALUE ( 'Account Relations'[Account] ) <> 96910
&& VALUE ( 'Account Relations'[Account] ) <> 96950
) ,
KEEPFILTERS( VALUE('Main Project Master File - PMF'[PMF Project Type] ) >= 30 &&
VALUE( 'Main Project Master File - PMF'[PMF Project Type] ) <= 69
)
)
RETURN
_Amount

[asjones]

hmmm still stummped .. the Keepfilters and other stuff ist standard stuff .... so not sure why this won't work.

[asjones]

hmmmm as i can't get this to work CALCULATE( SUM(Fact Table[amount]) , Dim Table[Color] = "Green") as it returns nothing.

[AlB]

Hi @asjones 

Whats the problem with the code you show? Looks fine to me.

I'm assuming yur relationship is unidirectional from the Dim to the fact table

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers