How to look for data from a single cell in column "a", in the whole of column "b".

[Anonymous]

Hi Experts, 
I would like to look for a single cell of data in column "locator", in the whole column "order" - not just vertically. 
I then need it to result in "Yes", if there's a match and "No" if there is not. 

I have tried many different Dax functions (Contains, Search, Valuelookup) etc but can't get the correct result. 

The problem I have is the matching data sits in a different row - example below. 
Please note: they are set as text fields, not numbers.  And they need to remain as text. 

Thanks in advace. 

Locator	Order	Result
1	0	yes
1	0	yes
1	0	yes
1	0	yes
1	0	yes
1	0	yes
	1	yes
	1	yes
	1	yes
	1	yes
2	0	no
2	0	no
2	0	no
2	0	no
2	0	no
2	0	no
3	0	yes
3	0	yes
3	0	yes
3	0	yes
	3	yes
	3	yes
	3	yes
4	0	no
4	0	no
4	0	no
4	0	no
4	0	no
4	0	no

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this column

Column =
CONTAINS ( VALUES ( 'TableName'[Order] ), 'TableName'[Order], [Locator] )
    || CONTAINS ( VALUES ( 'TableName'[Locator] ), 'TableName'[Locator], [Order] )

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(COUNTA(Data[Order]),FILTER(Data,Data[Order]=EARLIER(Data[Locator])))),"No","Yes")

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur  - thank you.  This worked perfect with my nested IF statements, leaving the cells blank when the if statements are not true. 

B2B OPEN ORDER = IF('Master SOH'[Inventory Month]="CURRENT",
IF('Master SOH'[Subinventory Code]="B2B",
if(ISBLANK(CALCULATE(COUNTA('Master SOH'[Order Number]),FILTER('Master SOH','Master SOH'[Order Number]=EARLIER('Master SOH'[Locator Code])))),"No","Yes")))

[Ashish_Mathur]

You are welcome.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this column

Column =
CONTAINS ( VALUES ( 'TableName'[Order] ), 'TableName'[Order], [Locator] )
    || CONTAINS ( VALUES ( 'TableName'[Locator] ), 'TableName'[Locator], [Order] )

[Anonymous]

@Zubair_Muhammad  - thanks very much, this works perfect.

Would you mind to explain what | | part of the syntax does / means?

I have a different problem with the same data, which I thought I knew how to solve..

First I look at the Inventory Month column and only want to see a 'TRUE' if the result is "CURRENT" & the CONTAINS formula is 'TRUE' this works perfect as it is. 
Next I look at the Subinventory Code, and only want a 'TRUE' if = B2B and as above.  
If Subinventory Code is not "B2B", I want the result to be an empty cell/blank.. and not 'FALSE'.. 

Is this possible? 

B2B OPEN ORDER = IF('Master SOH'[Inventory Month]="CURRENT",
IF('Master SOH'[Subinventory Code]="B2B",
CONTAINS(values('Master SOH'[Order Number]),'Master SOH'[Order Number],'Master SOH'[Locator Code])
|| CONTAINS(VALUES('Master SOH'[Locator Code]),'Master SOH'[Locator Code],'Master SOH'[Order Number])))

Inventory month	Subinventory	Locator	Order	Result
Current	B2B	1	0	yes
Current	B2B		1	yes
Current	B2B	2	0	no
Current	B2B	3	0	yes
Current	B2B		3	yes
Current	B2B	4	0	no
May	B2B	2		no
May	B2B		2	no
CURRENT	ABC

[Zubair_Muhammad]

@Anonymous 

Try this

B2B OPEN ORDER =
VAR Orders =
    COUNTROWS (
        FILTER (
            'Master SOH',
            'Master SOH'[Order Number] = EARLIER ( [Locator Code] )
                && [Inventory month] = EARLIER ( [Inventory month] )
                && [Subinventory Code] = EARLIER ( [Subinventory Code] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Subinventory Code] <> "B2B", BLANK (),
        Orders > 0
            && [Inventory month] = "Current", "Yes",
        "No"
    )

[Anonymous]

Hi @Zubair_Muhammad , 

This is great - thanks so much.  Would you mind to explain what the | | part of the syntax means/does? 

I have another challange, which I didnt raise first becuase I thought I knew the soltuion.. (it partly works)

This data includes multiple inventory months, and multiple subinventory's.
I only want the result, 'True' if the order and locator appear in the 'CURRENT' inventory month (this appears to work) 

I also only want to see a result if the Subinventory = "B2B".   - if it does not, I want the cell to remain blank/empty. 

At the moment, all cells with a different subinventory than "B2B" results in "False. 

This is my currnet syntax:

B2B OPEN ORDER = IF('Master SOH'[Inventory Month]="CURRENT",
IF('Master SOH'[Subinventory Code]="B2B",
CONTAINS(values('Master SOH'[Order Number]),'Master SOH'[Order Number],'Master SOH'[Locator Code])
|| CONTAINS(VALUES('Master SOH'[Locator Code]),'Master SOH'[Locator Code],'Master SOH'[Order Number])))

Inventory month	Subinventory	Locator Code	Order Number	B2B OPEN ORDER
Current	B2B	1	0	yes
Current	B2B		1	yes
Current	B2B	2	0	no
Current	B2B	3	0	yes
Current	B2B		3	yes
Current	B2B	4	0	no
May	B2B	2		no
May	B2B		2	no
Current	ABC	123

[Anonymous]

Hi @Zubair_Muhammad , 

This is great - thanks so much.  Would you mind to explain what the | | part of the syntax means/does? 

I have another challange, which I didnt raise first becuase I thought I knew the soltuion.. (it partly works)

This data includes multiple inventory months, and multiple subinventory's.
I only want the result, 'True' if the order and locator appear in the 'CURRENT' inventory month (this appears to work) 

I also only want to see a result if the Subinventory = "B2B".   - if it does not, I want the cell to remain blank/empty. 

At the moment, all cells with a different subinventory than "B2B" results in "False. 

This is my currnet syntax:

B2B OPEN ORDER = IF('Master SOH'[Inventory Month]="CURRENT",
IF('Master SOH'[Subinventory Code]="B2B",
CONTAINS(values('Master SOH'[Order Number]),'Master SOH'[Order Number],'Master SOH'[Locator Code])
|| CONTAINS(VALUES('Master SOH'[Locator Code]),'Master SOH'[Locator Code],'Master SOH'[Order Number])))

Inventory month	Subinventory	Locator Code	Order Number	B2B OPEN ORDER
Current	B2B	1	0	yes
Current	B2B		1	yes
Current	B2B	2	0	no
Current	B2B	3	0	yes
Current	B2B		3	yes
Current	B2B	4	0	no
May	B2B	2		no
May	B2B		2	no
Current	ABC	123