Forum Discussion

VenkatTav's avatar
VenkatTav
Frequent Visitor
3 years ago

Performance issue with dax

Hi All,

 

In model i did not give the relationship between the tables and created relationship using NaturalInnerjoin in dax, but below code is running for ages and errors out with exceeded  resources in power bi service and but in desktop runs for 20 to 30mins which is very bad perfoormance.

 

Cardinality is many to many between the tables in DB.

 

Please help to optimise the dax.

 

MISS_ITEM_RCV(205)_1 = CALCULATE(MAX(FORECLOSURE_STATUS[STATUS_DATE]),( FILTER ( NATURALINNERJOIN (FORECLOSURE_STATUS, SERVICE_CODE ), SERVICE_CODE[SERVICE_CODE] = "DH1" || FORECLOSURE_STATUS[STATUS_TYPE] IN { "90", "243", "205" })))

 

Thanks in advance.

Need Help
Tips and Tricks

1 Reply

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Why not create relationships? If you don't want to have an active relationship you could use CROSSFILTER with an inactive one or use TREATAS with no relationship. In any case, doing the join on the full tables take a lot of memory. Try to limit it to just the columns you need if you can't get there with CROSSFILTER or TREATAS.


    Pat