Forum Discussion

srlabhe's avatar
srlabhe
Super User
10 months ago
Solved

Implementing Snowflake SQL in PBI Import(NOT DQ as its not suggested)

Hi All, Actually I am trying to implement below query in Power BI Import(As dont wana use DQ with Dynamic parameter passed) SELECT Table1.*,'mapped', FROM Table1 WHERE as_of_date <= '2025-09-17'...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi srlabhe ,

    Thank you for the update. You want all latest dates(as_of_date) records for any Mapped ID/Unmapped ID. Please refer below output snap and attached PBIX file.

     

     

    In Slicer select on "2025-09-17", it will display the mapped records (latest date <= slicer date).

     

    EntityID       Dates <= 2025-09-17      Latest Date      Value
    A1                         09-10, 09-15               09-15            110
    A2                         09-12, 09-17               09-17            210


    In Slicer select on "2025-09-17", it will display the unmapped records (Earliest Date >= slicer date).

     

    EntityID             Dates >= 2025-09-17        Earliest Date    Value
    B1                           09-17, 09-19, 09-21       09-17              300
    B2                           09-18                             09-18              410

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

srlabhe's avatar
srlabhe
Super User
10 months ago

Thanks what should be SomeValue in 4th step above ?

v-dineshya's avatar
v-dineshya
Community Support
10 months ago

Hi srlabhe , Thank you for the update.

In DAX, the MAX(Data[SomeValue]) is the value you want to return from that latest row such as metrics like Price, Balance, Quantity, a text column like Status, Category or an ID if you are returning identifiers.

 

For example:

 

MappedID       as_of_date     PM_IS_ACTIVE     Amount
A1                      2025-09-10           Y                 100
A1                      2025-09-15           Y                 120
A1                      2025-09-18           Y                 140
B1                      2025-09-12           Y                  80
B1                      2025-09-17           Y                  90

 

If your slicer selects '2025-09-16', you want to return the latest record ≤ that date.
Then MAX(Data[Amount]), returns 120 for A1 and 90 for B1, because those are the latest as_of_date ≤ 2025-09-16.

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh

 

  • srlabhe's avatar
    srlabhe
    Super User
    10 months ago

    This LatestValue measure should be crated on LatestRecords data right ?

    • v-dineshya's avatar
      v-dineshya
      Community Support
      10 months ago

      Hi srlabhe  Yes,  LatestValue measure should be created on LatestRecords data.  Please do let us know if you have any further queries.

       

      Regards,

      Dinesh

      • srlabhe's avatar
        srlabhe
        Super User
        10 months ago

        Thanks but its npt working as inteded as it should show all latest dates(as_of_date) records for any Mapped ID/Unmapped ID