Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Lookupvalue with multiple values in multiple tables

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-juanli-msft
Community Support
7 years ago

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