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:

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

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:

EffectiveDateProdVersionID
1/31/20243
2/29/20242
3/31/20245
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:

     

     

6 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper 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 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.

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

  • 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: