Level of detail with specific logic conditions

[cbschley]

Hello,

I am trying to perform a "level of detail" calculation.

In the example below, I want the "Modified_DC" field to display "MX05" for all rows, based on the Ship_From_DC where Master_Item = Material_ID.

I believe this can be done using eithe ALLSELECTED or ALLEXCEPT, but I haven't been able to figure this out. 

[Anonymous · Accepted solution]

Hi,

Thanks for the solution parry2k offered, and i want to offer some more information for user to refer to.

hello @cbschley, you can create the following calculated column.

Modified_DC =
VAR a = [MASTER ITEM]
VAR b = [LOCATIONID]
RETURN
    CALCULATE (
        MAX ( 'Table'[SHIP_FROM_DC] ),
        FILTER ( 'Table', 'Table'[MASTERALID] = a && 'Table'[LOCATIONID] = b )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@cbschley yes, if you are planning to add it as a column, better to add at the source (always a best practice to bring data as upstream as possible, and as close to the source). That would be a proper solution.

[parry2k]

@cbschley try this measure:

Modified_DC  = 
CALCULATE (
    MAX ( Table2[Ship_from_DC] ),
    FILTER (
        ALLSELECTED  ( Table2 ),
        Table2[Master_Item] = Table2[Material_Id] 
    ),
    VALUES ( Table2[Master_Item] )
)

[cbschley]

Hi @parry2k , I tried that, its now giving me a single value, but not the correct one. I would expect this to display "MX05" for that column:

[parry2k]

Are you adding this as a measure or a column? It is a measure.

[cbschley]

I am trying to add it as a column, because I need to create joins on this new column. Perhaps its best I just bring it up to the DB layer to avoid any issues.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result there.