Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Blank Row Filter

Hello All,

I am a new power bi user and I am having problem solving this problem. I have 2 tables both connected to each other by ID. Now I have used a report level filter on Client name column( of table 1 ) to filter american bank (see the image below) and so my table 2 filters all banks that have same ID as that of American bank but  in case of blank ID i want only selected bank (American Bank not Wells fargo) along with the other banks that have matched NOTBLANK ID.

How can I achieve this?

Thanks in advance

Regards

Chaha

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous 

     

    Based on my test ,the result is only two rows when select "AMERICAN BANK" in report level filter having relationship between two tables on original [Id].

     

     

     

     

     

     

     

     

     

     

    So ,recommend you create new Id columns in two table to filter the required value, and then create relationship between the two tables on Table1[Id_new1]=Table2[Id_new2].

     

    Table1: Id_new1 = IF(Table1[Id]=BLANK(),0,Table1[Id])

                  

    Table2: Id_new2 = IF(Table2[Id]<>BLANK(),Table2[Id],IF(Table2[Client name]=MAX(Table1[Client name]),0,BLANK()))

     

     

     

     

     

     

     

    Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZac5lryuChEmcn82cRsLd0B7lPdCzyjDKHkcGVYVfIfoA?e=e6xzdz

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous 

     

    Based on my test ,the result is only two rows when select "AMERICAN BANK" in report level filter having relationship between two tables on original [Id].

     

     

     

     

     

     

     

     

     

     

    So ,recommend you create new Id columns in two table to filter the required value, and then create relationship between the two tables on Table1[Id_new1]=Table2[Id_new2].

     

    Table1: Id_new1 = IF(Table1[Id]=BLANK(),0,Table1[Id])

                  

    Table2: Id_new2 = IF(Table2[Id]<>BLANK(),Table2[Id],IF(Table2[Client name]=MAX(Table1[Client name]),0,BLANK()))

     

     

     

     

     

     

     

    Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZac5lryuChEmcn82cRsLd0B7lPdCzyjDKHkcGVYVfIfoA?e=e6xzdz

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.