Lookupvalue with multiple values in multiple tables

[Anonymous]

Still pretty new to DAX and I would so appreciate some help.   I have a pretty complex set of rules to find out a commit time for orders.   Four columns - Brand Name, Ship From, FGO.StyleCode - each of these chould be a combination of things with the fourth CO.RROStatus being either APPRVD, PEND, REJECT, or REVIEW - the only one here that will be a different return will be REJECT - it will go to a different table and return a specific lead time, the others will go to a separate table and return the lead time for the specific first three parameters in a separate table.  I have included a sample of the tables (attached) and  the way they are related , the result table I wish for the "leadtime" column to be created in and the DAX Code I have tried below to create the calculated column:

TotalOrders[LeadTime] = LOOKUPVALUE( 
    TotalOrders[LeadTime], 
            RELATED(TotalOrders[Customer Brand.Brand Name], CommitPolicies[BrandID]), 
            RELATED(TotalOrders[ShipFrom], CommitPolicies[ShipFrom]), 
            RELATED(TotalOrders[Finished Goods Ordered.Door Style Code], CommitPolicies[Style]),
                IF(TotalOrders[CO.RROStatus] = "REJECT", RRORejLeadTimes[ReJectPckgLeadTime], 
                        , RROLeadTimes[PckgLeadTime])
                            )

Each row is a separate order and I would like to have the last field (LeadTime) populated by the DAX above.

If anyone has a better idea of how this should be done, I am all about it!   THANKS in advance!

[v-juanli-msft]

Hi @Anonymous

It seems a bit complex from your description.

Let me tell you what i understand and please tell me if i'm right.

1.Table "TotalOrders" has relationship with table "CommitPolicies"( not directly, their relationship is via table "ShipFrom", "Customer Brand.Brand Name", "StyleUnqiue")

2. you want to get related column for table "TotalOrders" from table "CommitPolicies" or "RRORejLeadTimes",

when the TotalOrders[CO.RROStatus] =APPRVD, PEND, or REVIEW, it should get related column from  table "CommitPolicies",

when the TotalOrders[CO.RROStatus] =REJECT, it should get related column from  table "RRORejLeadTimes".

Best Regards

Maggie

[Anonymous]

Maggie, 

First thank you for looking at my issue!   You are correct in your description of the problem.    However, I have scaled the entire project down to just one report and not as many tables to simplify due to the fact I was getting a memory issue after merging the three columns Commit Times - RRORejLeadTimes and RROLeadTimes.   This allowed me to Use certain fields in the Commit policies table instead of the "pass through" tables of Styles Unique, Ship From and CustomerBrandNameUnique.  My hope is to use the CO.RROStatus field in Total Orders to filter which .....LeadTime from the CommitPolicies table.  I have screen shots to further illustrate my endeavors. 

I am attempting the DAX to create a calculated column:

TotalOrders[LeadTime] = 
    IF( TotalOrders[CO.RROStatus] = "REJECT",
        && IF( TotalOrders[CustomerBrand.BrandName] = CommitPolicies[BrandID],
        && IF( TotalOrders[ShipFrom] = CommitPolicies[ShipFrom], 
        && IF( TotalOrders[FGO.StyleCode] = CommitPolicies[Style],
                [RRORejLeadTimes.ReJectPckgLeadTime], 
                CommitPolicies[RROLeadTimes.PckgLeadTime]))))
 

But it doesn't really work....still new at DAX and used to using SQL....

[v-juanli-msft]

Hi @Anonymous

Try this measure instead

TotalOrders[LeadTime] = 
    IF( TotalOrders[CO.RROStatus] = "REJECT"
         && TotalOrders[CustomerBrand.BrandName] = CommitPolicies[BrandID],
        && TotalOrders[ShipFrom] = CommitPolicies[ShipFrom]
        && TotalOrders[FGO.StyleCode] = CommitPolicies[Style],
                [RRORejLeadTimes.ReJectPckgLeadTime], 
                CommitPolicies[RROLeadTimes.PckgLeadTime]))))

If it doesn't help, please let me know.

Best Regards

Maggie