cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support

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

Capture.PNG 

 

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.

View solution in original post

@Mohit23 ,

 

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

 

Regards,

Jimmy Tao

View solution in original post

5 REPLIES 5
Community Support
Community Support

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

Capture.PNG 

 

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.

View solution in original post

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

Capture.PNG 

 

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 ,

 

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

 

Regards,

Jimmy Tao

View solution in original post

Thank you so much!

Solution Sage
Solution Sage

Hey @Mohit23 

Look at this.  

Or that.

I believe this is the solution you need.

Ta!
A

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors