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
Super User
2 years ago

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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.