cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TRIPPR Frequent Visitor
Frequent Visitor

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

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. 

LocatorOrderResult
10yes
10yes
10yes
10yes
10yes
10yes
 1yes
 1yes
 1yes
 1yes
20no
20no
20no
20no
20no
20no
30yes
30yes
30yes
30yes
 3yes
 3yes
 3yes
40no
40no
40no
40no
40no
40no
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

@TRIPPR 

 

Try this column

 

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

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

Hi,

This calculated column formula works

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

Hope this helps.

Untitled.png

8 REPLIES 8
Super User
Super User

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

@TRIPPR 

 

Try this column

 

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

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

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 monthSubinventoryLocator CodeOrder NumberB2B OPEN ORDER
CurrentB2B10yes
CurrentB2B 1yes
CurrentB2B20no
CurrentB2B30yes
CurrentB2B 3yes
CurrentB2B40no
MayB2B2 no
MayB2B 2no
CurrentABC123  
TRIPPR Frequent Visitor
Frequent Visitor

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

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 monthSubinventoryLocator CodeOrder NumberB2B OPEN ORDER
CurrentB2B10yes
CurrentB2B 1yes
CurrentB2B20no
CurrentB2B30yes
CurrentB2B 3yes
CurrentB2B40no
MayB2B2 no
MayB2B 2no
CurrentABC123  
Super User
Super User

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

Hi,

This calculated column formula works

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

Hope this helps.

Untitled.png

TRIPPR Frequent Visitor
Frequent Visitor

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

@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 monthSubinventoryLocatorOrderResult
CurrentB2B10yes
CurrentB2B 1yes
CurrentB2B20no
CurrentB2B30yes
CurrentB2B 3yes
CurrentB2B40no
MayB2B2 no
MayB2B 2no
CURRENTABC   

 

 

 

TRIPPR Frequent Visitor
Frequent Visitor

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

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")))
Super User
Super User

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

@TRIPPR 

 

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"
    )
Super User
Super User

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

You are welcome.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 924 guests
Please welcome our newest community members: