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 ...
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.

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.