FILTER function with indirect relationship

[admin_xlsior]

Hi guys,

Let say I have this 3 tables with diagram :

If I want to SUM the amount in my WorkFactTable with Group[Type] for example in that table has value "Type1", what is the DAX statement I should use ?

I tried this :

TotalAmount = SUMX ( FILTER ( WorkFactTable, RELATED ( Group[Type] ) = "Type1"), WorkFactTable[Amount] )

It give me error saying "The column "Group[Type]" either does not exist or doesn't have any relationship to anytable in the current context.

It's true that WorkFactTable doesn't have direct relation with Group, but I already put relationship between my fact table to the Work Order Dim (using Work Id) which then related to this table Group (using Group id) which I'm using it as the filter.

Should I created direct relationship between my WorkFactTable to GroupTable ? is RELATED function cannot detect indirect relationship ?

Please help

Thanks.

[parry2k]

@admin_xlsior relate function cannot be used with indirect filters. as best practice it will make sense to have group table direct relationship with fact table or you can use crossfilter function.

[admin_xlsior]

Hi,

Thanks for replying. Noted on the RELATED function limits. However when I google and look at the samples, it seems CROSSFILTER is just to turn on the relationship, am I right ? how to apply the filter it self ? 

Some example from Microsoft Docs:

CROSSFILTER(<columnName1>, <columnName2>, <direction>)

BiDi:= CALCULATE([Distinct Count of ProductKey], CROSSFILTER(FactInternetSales[ProductKey], DimProduct[ProductKey] , Both))

Still confuse, where I put the filter condition it self ?

Forgive me because I really a newbie in Power BI and DAX.

Thanks,

[parry2k · Accepted solution]

@admin_xlsior see attached file for solution. Hope this helps, feel free to reach out if need more assistance.

[Kat_Finra]

THANK YOU!

[admin_xlsior]

Hi,

It works. Just what I need. Thank you very much for your help.