cancel
Showing results for
Did you mean:

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

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
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
Employee

This calulcated column might be close

```Column =
VAR LastDateFromComparison =
MAXX(
FILTER(
V_ACCT,
'V_ACCT'[Comparison] = EARLIER('Outgoing'[Comparison])
)

RETURN    IF ( LastDateFromComparison > 'Outgoing'[Visit Date] , 1, 0)    ```

Proud to be a Datanaut!

Post Patron

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.

Employee

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 )```

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.
Post Patron

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

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

Employee

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?

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.

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

```Admit date =
LOOKUPVALUE (
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],
)```

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.
Post Patron

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

LOOKUPVALUE (
V_ACCT[Comparison], Outgoing[Comparison],
V_ACCT[Rank], Outgoing[ORank]
)

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors