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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kattlees
Post Patron
Post Patron

Add date comparison to look up column

I have two tables:
Outgoing and V_ACCT. I am trying to compare everyone that is in V_ACCT and also in Outgoing AND the V_ACCT[Admit_Date] >= Outgoing[Visit_Date]

 

I have the comparison on names to work, but need to add in the date comparison. Where and how do I add that part?

 

Example:

V_ACCT
Comparison               Admit_Date
Jane Doe                      1/2/18
Jim Bob                        11/15/17

 

Outgoing
Comparison                 VIsit Date
Jane Doe                      12/22/17
Jim Bob                        1/8/18

 

Only Jane Doe would show up on my list because her visit date was BEFORE her admit date

 


Match Flag =
IF(
IFERROR(
LOOKUPVALUE(
Outgoing[Comparison],
Outgoing[Comparison],
V_ACCT[Comparison]
),
0
) = 0,
0,
1
)

6 REPLIES 6
Phil_Seamark
Employee
Employee

Hi @kattlees

 

This calulcated column might be close

 

Column = 
VAR LastDateFromComparison = 
    MAXX(
        FILTER(
            V_ACCT,
            'V_ACCT'[Comparison] = EARLIER('Outgoing'[Comparison])
            )
            ,V_ACCT[Admit_Date])
            
RETURN    IF ( LastDateFromComparison > 'Outgoing'[Visit Date] , 1, 0)    

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

I get this error:
A single value for coumn 'Comparison' in table 'Outgoing' 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.

Hi @kattlees,

 

If you were referring to Phil_Seamark's solution, you should create a calculated column in 'Outgoing'  table with above formula rather than in 'V_ACCT' table.

 

To add a calculated column in 'V_ACCT' that checks whether current Comparison should be filtered out, please try:

Match Flag =
VAR visitdate =
    LOOKUPVALUE ( Outgoing[VIsit Date], Outgoing[Comparison], V_ACCT[Comparison] )
RETURN
    IF ( V_ACCT[Admit_Date] > visitdate, 1, 0 )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm still getting the same error.

 

Let's say I have this data

 

Outgoing

Comparison                  Visit Date

Jim Bob 05-15-1980      2-15-2018

Jim Bob 05-15-1980      3-10-2018

Sally Sue 08-12-16         2-15-2018

 

V_ACCT                            Admit Date

Jim Bob 05-15-1980        1-10-2017

Jim Bob 05-15-1980        3-11-2018

 

 

I would want the result of 

Jim Bob 05-15-1980        3-10-2018          3-11-2018

Hi @kattlees,

 

The error is caused by the many to many relationship between two tables. In your scenario, how to determine a unique Admit_Date which is compared with Visit Date for each person? As there are multiple visit date/admit date records per user.

 

Based on my assumption, the records highlighted in the same color are matched each other, right?

1.PNG

 

If that is the case, you should add index values inside each comparison group. For details, please see Custom column Index or Ranking by other column

 

Result.

1.PNG2.PNG

 

Then, add a calculated column in 'Outgoing' table:

Admit date =
LOOKUPVALUE (
    V_ACCT1[Admit_Date],
    V_ACCT1[Comparison], Outgoing[Comparison],
    V_ACCT1[Index], Outgoing[Index]
)

New a calculated table to get your desired output with this formula:

Result_Table =
SELECTCOLUMNS (
    FILTER (
        Outgoing,
        Outgoing[Visit Date] <> BLANK ()
            && Outgoing[Admit date] <> BLANK ()
            && Outgoing[Visit Date] > Outgoing[Admit date]
    ),
    "Comparison", [Comparison],
    "Visit date", [Visit Date],
    "Admit Date", [Admit date]
)

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greatly appreciate your help. My Admit Date in Outgoing has this value ( I did a rank in each table). 

Admit date = 
LOOKUPVALUE (
V_ACCT[visit_admit_date],
V_ACCT[Comparison], Outgoing[Comparison],
V_ACCT[Rank], Outgoing[ORank]
)

I get "A circular dependency was detected: Outgoing[Admit date], 2bbbd88e-1358-45e5-ad47-c7c0e84471ed, Outgoing[Admit date]."

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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