cancel
Showing results for
Did you mean:
Frequent Visitor

## If a value exist in one table and not other and vice versa

Hi All,

Trying to come up with a logic to do following:

If unique ID exist in May data  but not in Apr, then label it as "In" and show up \$ value under "In" column

If the unique ID exists in Apr data but not in May then label it as "Exit" and show up \$ value under "Exit" colummn

for everything else show 0.

Note: Data for two periods are appeneded

Below is more practical example of what I am trying to do and "Label", "In" and "Exit" are desired results.

Unique ID Month  \$                          Label            In  Exit

678            May     13                         0                   0   0

985            May     15                         In                  15 0

462            May     16                         0                   0   0

678            Apr      13                         0                   0    0

876            Apr      17                         Exit               0   17

462            Apr      22                         0                   0   0

I would really appreciate any help here.
Thanks

2 ACCEPTED SOLUTIONS Community Support

Create two calculate columns using DAX below:

```In =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0))

Exit =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))``` Community Support Team _ Jimmy Tao

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

Have you solved your issue right now? If you have, could you please kindly mark the correct answer?

Regards,

Jimmy Tao

5 REPLIES 5 Community Support

Create two calculate columns using DAX below:

```In =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0))

Exit =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))``` Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thank you so much for all your help!

@v-yuta-msft wrote:

Create two calculate columns using DAX below:

```In =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "May", Current_Value, 0))

Exit =
VAR Current_ID = Table1[Unique ID]
VAR Number_Of_ID = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Unique ID]))
VAR Current_Month = CALCULATE(MAX(Table1[Month]), FILTER(Table1, Table1[Unique ID] = Current_ID))
VAR Current_Value = CALCULATE(MAX(Table1[\$]), FILTER(Table1, Table1[Unique ID] = Current_ID))
RETURN
IF(Number_Of_ID >= 2, 0, IF(Current_Month = "Apr", Current_Value, 0))``` Community Support Team _ Jimmy Tao

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

Have you solved your issue right now? If you have, could you please kindly mark the correct answer?

Regards,

Jimmy Tao

Frequent Visitor

Thank you so much! Solution Sage

Hey @Mohit23

Look at this.

Or that.

I believe this is the solution you need.

Ta!
A   