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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors