Forum Discussion

Niikk's avatar
Niikk
Frequent Visitor
1 year ago
Solved

Getting column data from different tables depending on the date

Hi!

I had to split my transacation table into two tables, "Transaction_Old" (has all data prior to 2025) and "Transaction" (has all data from 2025-01-01 and onwards). I setup relations so I can filter on dates, agreements etc. Also remade my measures and all that work great. There is a DateTable[Date] column that have relations into both Transaction tables.

Combined with summaries per agreement and month I also have a table visual that shows row level transaction information, and here is where I'm stuck. Dates and values calculates ok and data from other tables with relations are no problem. However if I want to get the "Description" from the Transaction table it's a no go. If I show Transaction_Old[Description] as a column in my table but the date is from 2025-01-01 or newer I get no row information, same from Transaction[Description] if the date is before 2025-01-01. i don't want to add both columns, also there's 3 other columsn with same issue as "Description".

How do I write a DAX measure to check if a date is newer then the max date in my Transaction_Old table and if so get Transaction[Description] value and if not then get Transaction_Old[Description]? I have a measure that checks the max date "Transaction_Old" called "TransOldMax".

Need Help

3 Replies

  • Niikk's avatar
    Niikk
    Frequent Visitor
    1 year ago

    Hi and thank you for the fast responses! When putting one of these measures at a time in the table it simply loads forever. It's a very big table though. If putting them in Cards and selecting a single row then your solution works govind_021 while the second one turns out blank.

    I will probably make two reports and the users has to switch between them when tracking on a transaction row level. Big thnx!!

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Niikk - As per my understanding, to check dynamic whether the date falls before or after the maximum date with transaction old with correct description, use selectedvalue for date and max function 

     

    check the below measure:

    DescriptionMeasure =
    VAR TransOldMaxDate = MAX(Transaction_Old[Date]) -- Get the max date from Transaction_Old
    VAR CurrentDate = SELECTEDVALUE(DateTable[Date]) -- Get the current date in the context of the table visual
    RETURN
    IF(
    CurrentDate <= TransOldMaxDate,
    SELECTEDVALUE(Transaction_Old[Description]), -- Use Transaction_Old[Description]
    SELECTEDVALUE(Transaction[Description]) -- Use Transaction[Description]
    )

     

    Replace both Transaction_Old[Description] and Transaction[Description] columns in your table visual with the new DescriptionMeasure.

  • govind_021's avatar
    govind_021
    Super User
    1 year ago

    Hey Niikk 
    please check out my solution

    DynamicDescription =
    VAR TransOldMaxDate = MAX('Transaction_Old'[TransactionDate])
    RETURN
    IF(
    SELECTEDVALUE('DateTable'[Date]) > TransOldMaxDate,
    SELECTEDVALUE('Transaction'[Description]),
    SELECTEDVALUE('Transaction_Old'[Description])
    )

    Regards
    Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
    Linkdin : www.linkedin.com/in/govind-sapkade-845104225
    Youtube : http://www.youtube.com/@govind_dataanalyst