Bypassing the RELATED() function for inactive relationships

[Anonymous]

I need help with part of my model.

Part of my model looks like this (the identical column names are the ones related to each other): 

My problem is that there are Raw Data entries that don't have an Order ID, but are still important in the visuals, which get filtered out when I use "Line Names" as a Slicer filter in my visualization, beacuse of the current active relationships.

I can solve this by switching the active relationship from Lines-to-Orders to Lines-to-Raw Data.

Unfortunately, my calculated column in the Orders table relies on this current active relationship and looks like this:

Max Order Speed (cases/min) =

MIN(

    MIN(

        DIVIDE(

            VALUE(RELATED(Lines[Max Speed Filler (pcs/min)])),

            Orders[Products per Case],

            0

        ),

    VALUE(RELATED(Lines[Max Speed Packer/Palletizer (cases/min)]))

    ),

    DIVIDE(

        VALUE(RELATED(Lines[Max Speed MPU (t/h)])) * 1000, // convert t/h to kg/h

        RELATED(Products[Product Weigth]) * Orders[Products per Case] * 60, // convert to cases/min

        0

    )

)

When I change the active relationships, my RELATED() functions can't refer to the Lines table anymore.

I'd like to change my DAX expression to bypass this function, so my calculated column stays active when I change the active relationships.

I think that's easier than to keep the relationships as is and in some way managing to not filtering out the raw data that doesn't have an Order ID...

I tried asking ChatGPT for help, but it just keeps trying adding in CALCULATE, USERELATIONSHIP, SUMX, FILTER (which requires me from switching from a calculated column to a measure), but without avail.

Any recommendations?

[Anonymous]

Thank you for your reply and you're right, normally I could've.

However, these are DirectQuery tables and LOOKUPVALUE isn't supported in DQ...

I managed to fix it by creating a new summarized table with the necessary columns from the other tables using LOOKUPVALUE and putting the calculated column in there.

It's not pretty, since I now have another table with half of the columns exactly the same as in the Orders table, but it works.

I could change the Orders table from DQ to Import, but the table is quite large and needs to be updated frequently, so I rather not (based on what I've read on DQ vs. import).

Or would you disagree?

[johnt75]

I would always go for import where possible. The speed of queries is way better than direct query and you don't get the limitations that DQ introduces.

If your orders table only changes by adding new rows, as opposed to making changes to existing rows, then you could look into incremental refresh if you're concerned about the time taken to do a data load, but Power BI can handle a lot of rows pretty quickly. 

It might be an idea to do some tests. Create a version of the dataset which is fully import mode and see how long it takes to refresh the model.

[johnt75]

You could use LOOKUPVALUE based on the Line Name.