Forum Discussion
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.50 | .053 | 4 |
| 2/29/2024 | 118.50 | .021 | 1 |
| 2/29/2024 | 120.50 | .025 | 2 |
However, I only want to illustrate/filter the "production" VersionID for each month by using an Input/Lookup Table that will be manually updated with the "production" VersionID. Example:
| EffectiveDate | ProdVersionID |
| 1/31/2024 | 3 |
| 2/29/2024 | 2 |
| 3/31/2024 | 5 |
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:
6 Replies
It is not clear as
(a) * the input data does not have 3/31/2024 which is used in your output
* the max version for 1/31/2024 is 4 and your output has 3.
What is the expected output?
If you are expecting like a table with summarized values for each month, then you can do these.
(b) Create Column
YYYYmm = FORMAT('Table'[EffectiveDate], "YYYYmm")and mark the column as "Hide in report view"
(c) Create new table and copy this
Table 2 = var _t1 = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[VersionID], 'Table'[YYYYmm] ) , "Effective Date", CALCULATE( max('Table'[EffectiveDate]), FILTER('Table', [YYYYMM] = EARLIER([YYYYMM] ))) , "Prod Version", CALCULATE( MAX('Table'[VersionID]), FILTER('Table', [YYYYMM] = EARLIER([YYYYMM] ))) ) RETURN SUMMARIZE(_t1,[Effective Date], [Prod Version])Output:
Hope this helps!
sevenhills Correct, I inadvertently left out 3/31 in the input data but hypothetically there would be values there. You're also correct that the input data contains versionid's up to 4, but that's exactly why I need to use the lookup table so that the input data knows which versionId to output, based off of what is listed in the lookup table.
sevenhills Seems like there's something being lost in translation. Using the SQL data table (Table1), I want to filter the output to only include rows that match the 'EffectiveDate' and 'VersionID' with the 'EffectiveDate and 'ProdVersionId' from the lookup table (Table2) that I will be manually updating to only include the VersionID that I want in my output from the SQL table. And I don't just want to filter out the Max EffectiveDate, but all of the rows that match the 'EffectiveDate' and 'ProdVersionId' from my lookup table.
If this is my SQL output (Table1):
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.50 .053 4 2/29/2024 118.50 .021 1 2/29/2024 120.50 .025 2 3/31/2024 225.50 .055 1 3/31/2024 230.25 .045 3 3/31/2024 231.50 .050 5 And this is my manually updated/maintained lookup table (Table2):
EffectiveDate ProdVersionID 1/31/2024 3 2/29/2024 2 3/31/2024 5 Using Table2 to filter the output from Table1, this is what I expect my output to be (Table3):
EffectiveDate Balance Rate VersionID 1/31/2024 522.50 .052 3 2/29/2024 120.50 .025 2 3/31/2024 231.50 .050 5 Do you want it in Power Query or DAX?
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:
