cancel
Showing results for
Did you mean:
Post Prodigy

## Countifs and search with left in Power BI

I have a two tables are Data and Report.

In Data and Report table contain the following columns are Item and area code.

In data table the item and area code contain prefix and suffix or prefix or suffix.

In report table the item column contain (Prefix and suffix) and area code columns contain prefix only.

If item column has prefix and suffix then it’s start "090 BR_-"

I am trying to match item and area code based on the prefix only from data table into report table (without helper column).

I am looking for two different result one is area code which is based on the item and area code in-between two tables and another one is YES or No result which is based on the item and area code in-between two tables.

In Excel I am applying the following formula =IF(COUNTIFS(DATA!\$C\$2:\$C\$2000,"*"&LEFT(\$A2,SEARCH("-",\$A2&"-")-1)&"*",DATA!\$G\$2:\$G\$2000,"*"&LEFT(\$B2,SEARCH("-",\$B2&"-")-1)&"*"),LEFT(\$B2,SEARCH("-",\$B2&"-")-1),IF(COUNTIFS(DATA!\$C\$2:\$C\$2000,"*"&LEFT(\$A2,SEARCH("-",\$A2&"-")-1)&"*",DATA!\$G\$2:\$G\$2000,""),"","NO"))

Can you please advise how can I get the same result in power BI by using New calculated column option.

Herewith attached the PBI file for your reference https://www.dropbox.com/s/mhgqcy2nk13icbp/R.pbix?dl=0.

1 ACCEPTED SOLUTION
Solution Sage

I am not sure that I properly understood your requirement about how you are matching between these two tables. But based on what I have understood, I have written the DAX code for the two calculated columns which are given below..

Try it in your PBIX and let me know if you need any changes. Considering that you have written a complicated formula in Excel, I am sure that you will be able to modify it to your requirements.

``````Desired Result 1 =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR CheckInData =
COUNTROWS(
FILTER(
DATA,
DATA[AREA CODE]=CurArea &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
)
VAR Result = IF(ISBLANK(CheckInData),"NO",CurArea)
RETURN
Result``````

``````Desired Result YesNo =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR CheckInData =
COUNTROWS(
FILTER(
DATA,
DATA[AREA CODE]=CurArea &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
)
VAR Result = IF(ISBLANK(CheckInData),"No","Yes")
RETURN
Result``````

7 REPLIES 7
Solution Sage

I am not sure that I properly understood your requirement about how you are matching between these two tables. But based on what I have understood, I have written the DAX code for the two calculated columns which are given below..

Try it in your PBIX and let me know if you need any changes. Considering that you have written a complicated formula in Excel, I am sure that you will be able to modify it to your requirements.

``````Desired Result 1 =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR CheckInData =
COUNTROWS(
FILTER(
DATA,
DATA[AREA CODE]=CurArea &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
)
VAR Result = IF(ISBLANK(CheckInData),"NO",CurArea)
RETURN
Result``````

``````Desired Result YesNo =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR CheckInData =
COUNTROWS(
FILTER(
DATA,
DATA[AREA CODE]=CurArea &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
)
VAR Result = IF(ISBLANK(CheckInData),"No","Yes")
RETURN
Result``````

Post Prodigy

Thanks for your reply and sorry for the inconvenience to understand my query.

You solution is working well but there is small error, in data table the area code is blanks according to the item then the same thing need to be return in report table.

Your both DAX is return No but actual result is blanks in report table. I try to modify your DAX but I am receving error can you please modify according to my desired result.

Solution Sage

In "Data" table, the area cod is blank.

But in the report table, the area code is AB005 which is not matching with blank() which is why the results are showing as "No".

What is that you need? Do you want to treat blanks() in the Data table's area field as match cases?

In that case, modify the condition of FILTER as follows...

``````Desired Result =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR CheckInData =
COUNTROWS(
FILTER(
DATA,
(DATA[AREA CODE]=CurArea || DATA[AREA CODE]="") &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
)
VAR Result = IF(ISBLANK(CheckInData),"NO",CurArea)
RETURN
Result``````

Solution Sage

Or you can try these codes also

``````Desired Result =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR FilteredTable =
FILTER(
DATA,
(DATA[AREA CODE]=CurArea || DATA[AREA CODE]="") &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
VAR CheckInData =
COUNTROWS(FilteredTable)
VAR CheckBlank =
CONCATENATEX(FilteredTable,[AREA CODE])=""
VAR Result = IF(ISBLANK(CheckInData),"NO",IF(CheckBlank,BLANK(),CurArea))
RETURN
Result``````
``````Desired Result =
VAR CurItem = LEFT(REPORT[ITEM],SEARCH("-",REPORT[ITEM],1,LEN(REPORT[ITEM])+1)-1)
VAR CurArea = LEFT(REPORT[AREA CODE],SEARCH("-",REPORT[AREA CODE],1,LEN(REPORT[AREA CODE])+1)-1)
VAR FilteredTable =
FILTER(
DATA,
(DATA[AREA CODE]=CurArea || DATA[AREA CODE]="") &&
NOT(ISBLANK(SEARCH(CurItem,DATA[PART NUMBERS],1,BLANK())))
)
VAR CheckInData =
COUNTROWS(FilteredTable)
VAR CheckBlank =
CONCATENATEX(FilteredTable,[AREA CODE])=""
VAR Result = IF(ISBLANK(CheckInData),"NO",IF(CheckBlank,BLANK(),CurArea))
RETURN
Result``````

...

Post Prodigy

Yes you are right it's not matched according to my condition and logic therefore the results is No.

The item are available in both tables but there's no are code in report table so this is different issue. If it possible can you please change the logic and show me as a blanks were area code is blanks so it will help to address the others problem in same report.

Thank you.

Solution Sage

Yes, I have given the revised DAX codes in my last post. Please refer to them.

Post Prodigy

Hi,

Thank you so much for your reply and modifying the DAX according to my requirements. This is perfect.

Thank you.

Announcements