Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@Anonymous
Try this column
Column = CONTAINS ( VALUES ( 'TableName'[Order] ), 'TableName'[Order], [Locator] ) || CONTAINS ( VALUES ( 'TableName'[Locator] ), 'TableName'[Locator], [Order] )
Hi,
This calculated column formula works
=if(ISBLANK(CALCULATE(COUNTA(Data[Order]),FILTER(Data,Data[Order]=EARLIER(Data[Locator])))),"No","Yes")
Hope this helps.
Hi,
This calculated column formula works
=if(ISBLANK(CALCULATE(COUNTA(Data[Order]),FILTER(Data,Data[Order]=EARLIER(Data[Locator])))),"No","Yes")
Hope this helps.
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.
@Anonymous
Try this column
Column = CONTAINS ( VALUES ( 'TableName'[Order] ), 'TableName'[Order], [Locator] ) || CONTAINS ( VALUES ( 'TableName'[Locator] ), 'TableName'[Locator], [Order] )
@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 |
@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" )
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 |
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |