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:

     

     

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: