Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Try this column

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Try this column

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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   

 

 

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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  
Anonymous
Not applicable

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  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.