Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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 o...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

 

 

 

 

 

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi,

       

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

       

      Thank you.