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

[Mohit23]

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 

[v-yuta-msft · Accepted solution]

@Mohit23 ,

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.

[v-yuta-msft · Accepted solution]

@Mohit23 ,

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.

[Mohit23]

Thank you so much for all your help! 

---

@v-yuta-msft wrote:

@Mohit23 ,

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.

---

[v-yuta-msft · Accepted solution]

@Mohit23 ,

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

Regards,

Jimmy Tao

[Mohit23]

Thank you so much!

[AClerk]

Hey @Mohit23 

Look at this.  

Or that.

I believe this is the solution you need.

Ta!
A