CODEWITHSUNDEEP
excelpowerbipowerquery
Marik
Marik

Reputation: 1

Compare values from two columns and return in 3rd column if there is mach

I need to compare and mach a value from column B to a values from column A. The criteria I need to fulfil are.

  1. The comparison and mach should be performed for standard timestamps. (Starting point of column A values need to be timestamp of value from column B that need to be looked for. And stop time a month of registers of column A value after start point. Or 50 values of column A starting from same row as the comparison valu from column A)

Hope it makes sense. I did that on excel but I am trying to do it on Power Bi or excel query

Example:

look for value B1 in the rangeA1-A51 if found return "Yes" otherwise "no" in C1 ( is it easier to check the range of A column values from the date Column ?)

Look for B2 in range B2-B52 if found return "Yes" otherwise "no" in C2

Look for B3 in range B3-B53 if found return "Yes" otherwise "no" in C3 .........

Date  column A  column B  Column C
1       124       136       Yes    
2       245       268        No
3       567       456        Yes
4       136       744        No
5       566       909        Yes
6       456       888        No
7       555       434        No
8       909       111        No
9       439       222        Yes
.       ...       ...        ...
.       ...       ...        ...
.       ...       ...        .. 
48      481       333        No
49      222       767        No
50      989       321        No
51      790       015        No

Upvotes: 0

Views: 3042

Answers (1)

smpa01
smpa01

Reputation: 4282

If you want this to be achieved through a DAX calculated column

Column C =
IF ( ( 'Table'[column B] ) IN VALUES ( 'Table'[column A] ), "Yes", "No" )

If you want this to be achieved through a DAX measure

Measure = 
VAR _lookUP = CALCULATE (
    MAX ( 'Table'[column A] ),
    FILTER (
        ALL ( 'Table' ),
        ( 'Table'[column A] ) IN SUMMARIZE ( 'Table', 'Table'[column B] )
    )
)
RETURN IF(_lookUP=BLANK(),"No","Yes")

Upvotes: 1

Related Questions