Filter Parameter Using Lookup Table

[dashmarley11]

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

[dashmarley11]

@sevenhills 

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

[sevenhills]

Do you want it in Power Query or DAX? 

[sevenhills · Accepted solution]

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]

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!

[dashmarley11]

@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.

[dashmarley11]

@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.