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

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'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