Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jacqueline_Lim
New Member

IF DAX for date function

Hi all,

 

I would like to look up the [LastTransactionDate] from 'Account' table, and if it's bigger than the [ReactivationDate] from 'DormantAccountReactivation' table, then return value with [LastTransactionDate], else blank.

 

My DAX is as below:

First Transaction Date = IF('Account'[LastTransactionDate]>'Dormant Account Reactivation'[ReactivationDate],'Account'[LastTransactionDate],blank())
 
But I received error as below:
A single value for column 'LastTransactionDate' in table 'Account' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

So I tried below DAX and error was resolved:

First Transaction Date = IF(MAX('Account'[LastTransactionDate])>MAX('Dormant Account Reactivation'[ReactivationDate]),MAX('Account'[LastTransactionDate]),blank())

 

But the return value was WRONG, it picked up the latest date in the LastTransactionDate column to compare with the latest date in the ReactivationDate column, and return with the latest date in the LastTransactionDate column, which turned out the whole First Transaction Date column became the same date.

 

Can anyone please help?

 
1 ACCEPTED SOLUTION

hey @FreemanZ and @Dangar332 ,

 

Thanks for that! But I think I've finally figured it out.

 

Below is my final DAX:

First Transaction Date = IF(

LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode])>'Dormant Account Reactivation'[ReactivationDate],

LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode]),

BLANK())

 

My problem was, i have to pick up the LastTransactionDate column from another table and then compare it with the ReactivationDate column with my existing table, and return with either LastTransactionDate or BLANK.

 

It contains 2 conditions: (1) lookup up value, (2) if condition.

I just did this and the data looks fine to me. I hope it doesn't have any underliying issue that I haven't seen yet. *finger crossed*

View solution in original post

11 REPLIES 11
FreemanZ
Super User
Super User

Hi @Jacqueline_Lim ,

 

is the code for a measure or calculated column? How are the two mentioned table related?

Hi @FreemanZ ,

It's a column. The 2 tables are related via unique client code.

I'm hoping to see the table this way, the first transaction date will have data if the last transaction date is later than reactivation date.

Jacqueline_Lim_0-1698633418747.png

 

hi @Jacqueline_Lim 

if you use the same code for a measure and plot the same table visual with the measure. it shall work, or?

@FreemanZ ,

 

Which one?

What I need is, to lookup the LastTransactionDate in Account table, and check if it is later than the ReactivationDate in DormantAccountReactivation table, if it is, then return value as LastTransactionDate in Account table, else leave it as blank

This DAX gives me error:
First Transaction Date = IF('Account'[LastTransactionDate]>'Dormant Account Reactivation'[ReactivationDate],'Account'[LastTransactionDate],blank())

 

I've tried creating for a new measure and a new column, both didn't work.

 

The error I've gotten is:
A single value for column 'LastTransactionDate' in table 'Account' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

post some data and let us see how it shall work. 

 

Post Sample Data

UPDATE: @ImkeF wrote a fantastic article for the best way to post data to the forums: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

@FreemanZ , I've created an example, does it make sense?

 

Table 1:

ClientIDLastTransaction Date
130/10/2023
228/5/2021
39/6/2022
410/10/2023
521/12/2020

 

Table 2:

ClientIDReactivation Date
51/8/2023
42/8/2023
33/8/2023
24/8/2023
15/8/2023

 

Expected outcome:

ClientIDFirstTransactionDate
130/10/2023
2 
3 
410/10/2023
5 

or you create a calculated table like:

table = 
ADDCOLUMNS(
    VALUES(Table1[ClientID]),
    "Result",
    VAR _transactiodate = CALCULATE(MAX(Table1[LastTransaction Date]))
    VAR _reactivationdate = CALCULATE(MAX(Table2[Reactivation Date]))
    RETURN
        IF(_transactiodate>_reactivationdate, _transactiodate)
)

it worked like:

FreemanZ_1-1698646687753.png

 

hi, @Jacqueline_Lim 

 

if your  table 1 and table 2 are 1:1 then it might work

 

 

result1 = 
SWITCH(TRUE(),MAX(Table1[LastTransaction Date])>MAX(Table2[Reactivation Date]),MAX(Table1[LastTransaction Date]),"")

 

 

hi @Jacqueline_Lim 

how are table1 and table2 related?

hi @Jacqueline_Lim ,

 

supposing they are related on ClientID column, then try to plot a table visual with Table1[ClientID] column and a measure like:

 

measure = 
VAR _transactiodate = MAX(Table1[LastTransaction Date])
VAR _reactivationdate = MAX(Table2[Reactivation Date])
RETURN
    IF(_transactiodate>_reactivationdate, _transactiodate, "")

 

 

it worked like:

FreemanZ_0-1698646588716.png

 



hey @FreemanZ and @Dangar332 ,

 

Thanks for that! But I think I've finally figured it out.

 

Below is my final DAX:

First Transaction Date = IF(

LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode])>'Dormant Account Reactivation'[ReactivationDate],

LOOKUPVALUE(Account[LastTransactionDate],'Account'[PositionAccountNo],'Dormant Account Reactivation'[ClientCode]),

BLANK())

 

My problem was, i have to pick up the LastTransactionDate column from another table and then compare it with the ReactivationDate column with my existing table, and return with either LastTransactionDate or BLANK.

 

It contains 2 conditions: (1) lookup up value, (2) if condition.

I just did this and the data looks fine to me. I hope it doesn't have any underliying issue that I haven't seen yet. *finger crossed*

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.