Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Uxia
New Member

2 Tables - Show blank rows

Hello,

 

I am working in Power BI Desktop with Salesforce Objects, I have to combine 2 tables (relationship Many:1) 

 

  • Table 1 (Opportunities): contains all opportunities
  • Table 2 (Plan) : contains the opportunities that have a plan and all the data fields in the plan section.

What I am trying to show in my dashboard table are some fields of the Plan table for opportunities that have a plan and blanks in case the opportunities that don't have a plan.

 

Due to in the Plan table the opportunities with no plan do not exist, every time I include any field of the Plan Table in the dashboard automatically removes the opportuties with no plan.

 

I have tried to create a new column in Table 1, with the data of table 2 where the relationship field matched with function Related but it does not allowe me to create this relationship.

 

Could anyone please point me in the right direction with this?

 

Many Thanks,

 

Uxia

 

1 ACCEPTED SOLUTION

Hi @Uxia,


I test with your sample and I found if I create relationship, these blank score and rating records will auto hide. I try to use 'show items with no data' option but not work.


For your scenario, I'd like to suggest you break relationship and create calculate column or measure with lookupvalue function to find out related score and rating.

 

Reference link:

Favorite Quick & Easy DAX: Lookup Values

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Uxia,


Can you please share some sample data and expected result? It will be help for  clarify your scenario.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

Thanks for the reply, I cannot share the actual data as it is confidential but I made a simple example that illustrates the situation

  • This would be Table 1 with all the opportunitities:

 

Table_1.PNG

 

 

  • This would be Table 2 with the Plan and the data

     

  • This is what I want to achieve:

Want.PNG

  •   This is actually what I get:

 

Get.PNG

 

As you can see, only the Opportunities that exist in Table 2 are the ones shown in the final table, but I want all the opportunities in Table 1, and if these do not exist in Table 2 then blanks should appear in the missing rows.

 

Hope this clarifies.

 

Let me know if you need anything else.

 

Thanks again,

 

Uxia

 

 

 

Hi @v-shex-msft

 

Here the missing table 2:

Table2.JPG

 

Thanks,

 

Uxia

Hi @Uxia,


I test with your sample and I found if I create relationship, these blank score and rating records will auto hide. I try to use 'show items with no data' option but not work.


For your scenario, I'd like to suggest you break relationship and create calculate column or measure with lookupvalue function to find out related score and rating.

 

Reference link:

Favorite Quick & Easy DAX: Lookup Values

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

Thanks for the tip. I actually did that yesterday and it worked.

 

Thanks,

 

Uxia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.