cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saxon10
Post Prodigy
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
sreenathv
Super User
Super User

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

 

View solution in original post

7 REPLIES 7
sreenathv
Super User
Super User

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

 

View solution in original post

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. 

 

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

sreenathv_0-1618227389518.png

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

 

 

 

 

 

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

...

 

 

Hi, Thanks for your reply and advice. 

 

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. 

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

Hi,

 

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

 

Thank you.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.