Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter Parameter Using Lookup Table

I have SQL data that comes through like this: EffectiveDate Balance Rate VersionID 1/31/2024 519.50 .050 1 1/31/2024 525.50 .051 2 1/31/2024 522.50 .052 3 1/31/2024 523.5...
Need Help
Tips and Tricks
  • sevenhills's avatar
    sevenhills
    2 years ago

    If it is in DAX and assuming these both tables are not related, you can add these columns to the ManualTable.

     

    Version ID, I added for self check, you don't need!

     

    Columns:

     

     

    Version ID = LOOKUPVALUE('Table'[VersionID], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

Balance = LOOKUPVALUE('Table'[Balance], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

Rate = LOOKUPVALUE('Table'[Rate], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

     

     

    Sample output for first few rows:

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

sevenhills 

If this is my SQL output (Table1):

EffectiveDateBalanceRateVersionID
1/31/2024519.50.0501
1/31/2024525.50.0512
1/31/2024522.50.0523
1/31/2024523.50.0534
2/29/2024118.50.0211
2/29/2024120.50.0252
3/31/2024225.50.0551
3/31/2024230.25.0453
3/31/2024231.50.0505

 

And this is my manually updated/maintained lookup table (Table2):

EffectiveDateProdVersionID
1/31/20243
2/29/20242
3/31/20245

 

Using Table2 to filter the output from Table1, this is what I expect my output to be (Table3):

EffectiveDateBalanceRateVersionID
1/31/2024522.50.0523
2/29/2024120.50.0252
3/31/2024231.50.0505
  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    2 years ago

    Do you want it in Power Query or DAX? 

     

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      2 years ago

      If it is in DAX and assuming these both tables are not related, you can add these columns to the ManualTable.

       

      Version ID, I added for self check, you don't need!

       

      Columns:

       

       

      Version ID = LOOKUPVALUE('Table'[VersionID], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

Balance = LOOKUPVALUE('Table'[Balance], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

Rate = LOOKUPVALUE('Table'[Rate], 'Table'[VersionID], ManualTable[ProdVersionID], 'Table'[EffectiveDate], ManualTable[EffectiveDate])

       

       

      Sample output for first few rows: