**BUG** Show Items with no data in slicer not working

Status: Accepted
by JohnOCallaghan Regular Visitor ‎01-17-2018 03:55 AM - edited ‎01-17-2018 04:48 AM

On a PBI desktop report we have 2 slicers and 1 chart:

Visual 1 is a slicer based on the date dimension (date attribute) - Filter applied

Visual 2 is a slicer based on "Team" attribute - "Show items with no data" is enabled

Visual 3 is a chart, showing a "count" based on a secondary relationship with the date dimension with the "Team" on the axis

 

Slicer 2 displays all of the "Teams" despite the applied date filter when the viewing via PBI Desktop

 

When this report is published to our PBI Service, the "Show Items with no data" option does not work, resulting in "Teams" that are displayed in the chart (remember, it is using a count based on a secondary date relationship), not being displayed in Visual 2 (with "Show items with no data" enabled).

 

Is this a bug?

Status: Accepted
Comments
by JohnOCallaghan Regular Visitor
on ‎01-24-2018 12:50 AM

Hi @v-qiuyu-msft and @OB

 

I think the reason you are not able to replicate this issue both @OB and I describe using the sample file @OB provided is because @OBs sample file does not include the page filter he refers too.

 

I have applied this filter to the desktop sample (based on @OB description), and I can indeed replicate the issue:

SnipImage.JPGSnipImage2.JPG

by dev_Varun Visitor
on ‎01-24-2018 05:10 AM

Hi @v-qiuyu-msft, As @JohnOCallaghan said, We're able to replicate the issue by just adding a visual or page filter and selecting the option "Show Items With No Data". The screenshots from @JohnOCallaghan referes the difference. So, If you can please have a look and update us asap. As, We're loosing business because of this. Escalation will be highly appreciated. However, I've also shared the region just in case you need. Thanks.region.JPG

by dev_Varun Visitor
on ‎01-25-2018 02:27 AM

Hi @v-qiuyu-msft, Any update on this?

by dev_Varun Visitor
on ‎01-29-2018 04:30 AM

Hi Power BI Team, We're still waiting for a solution from your side. Can you please update on the same?

by JohnOCallaghan Regular Visitor
on ‎01-29-2018 04:36 AM

Hi Power BI Team (@v-qiuyu-msft), to echo @dev_Varun, we really would like to give our managers/users an update regarding this issue.

 

Thanks.

by Moderator v-qiuyu-msft
on ‎01-31-2018 12:27 AM

Hi all,

 

I have reported this issue internally: CRI 58650675. Will update here once I get any information. 

 

Best Regards,
Qiuyun Yu

by Vicky_Song Established Member
on ‎01-31-2018 12:30 AM
Status changed to: Accepted
 
by OB Frequent Visitor
on ‎02-02-2018 12:15 PM

All,

 

I got a follow-up from Microsoft support today based on what they got from the Production team. I'm posting it here FYI. The change is intentional to fix a related bug re Items with No Data and it's coming in PowerBI Desktop as well.  

 

The explanation is based on the mock-up file attached to one of my posts above.  My further tests with a combination of bidirectional cross filtering, Items with No Data and filters applied to page vs individual visuals raised some further questions, which I've just sent back to support .  I also found this white paper that I have to read yet, but maybe you'll find it beneficial:

https://powerbi.microsoft.com/en-us/blog/bidirectional-cross-filtering-whitepaper-2/

 

Table Priority has a one to many relationship with Objective. In modeling terms, they are directly related. This is important because filters flow from the one to the many table when we evaluate the queries. So a filter on any column from the Priority table will impact all columns of the Objective table. For example, if we were to build a table with Status and Objective and have a filter on Priority = A, we can only see Statuses and Objectives that match to Priority A. Enabling Show Items with No Data does not make a difference. This means Show Items with No Data respects the filters. Due to the direct relationship between Priority and Objective tables, any filter set on a column from Priority will impact any column selected from Objective table. It can be a Page filter, visual filter, slicer or drill through filter. They will all have the same impact. We are only seeing Statuses and Objectives for Priority 'A'. Show Items With No Data respects Priority 'A' filter.

 

Now if we look at the situation in reverse, and group on Priority but filter on Status. Due to the direct relationship between Priority and Objective tables, any filter set on a column from Objective will NOT impact any column selected from Priority table, because Objective is at the end of a Many relationship and filters flow from the one to the many. However, we have enabled Cross Filter Direction - BOTH on the relationship. This essentially means filters can flow from Objective to Priority. Just like on the previous example we saw Priority 'A' affecting the Statuses, here we see the Status affecting Priorities. This is due to the bidirectional crossfiltering and it is the intended behavior. If this behavior is not wanted, bidirectional cross filtering should be turned off. This means switch Cross Filtering to Single.

by JohnOCallaghan Regular Visitor
on ‎02-05-2018 01:15 AM

Hi @OB

 

My reading of this is they are saying the issue is not a bug, but "by design"? 

 

Am I misreading it?

by Moderator v-qiuyu-msft
on ‎02-05-2018 01:45 AM

Thanks @OB update this information.

 

@all I got the same response from PG team as well. You can see below explanation: 

 

 

 

This is an intentional behavior change in order to fix a bug we've had in our Show Items With No Data code. This change will also ship with PBI Desktop February and there will be a blog mentioning it when we release it. We chose to release it earlier in the service because it fixes an outstanding issue with bidirectional crossfiltering. I've attached a PBIX with examples and explanations ( https://www.dropbox.com/s/cb5fo0fo6f5ae5b/Test%20_outer%20join_items%20with%20no%20related%20data-Ex... ).

Let me explain what was happening before.

Table Priority has a one to many relationship with Objective. In modeling terms, they are directly related. This is important because filters flow from the one to the many table when we evaluate the queries. So a filter on any column from the Priority table will impact all columns of the Objective table. For example, if we were to build a table with Status and Objective and have a filter on Priority = A, we can only see Statuses and Objectives that match to Priority A. Enabling Show Items with No Data does not make a difference. This means Show Items with No Data respects the filters.
Due to the direct relationship between Priority and Objective tables, any filter set on a column from Priority will impact any column selected from Objective table. It can be a Page filter, visual filter, slicer or drill through filter. They will all have the same impact.
We are only seeing Statuses and Objectives for Priority 'A'. Show Items With No Data respects Priority 'A' filter.

Now if we look at the situation in reverse, and group on Priority but filter on Status.
Due to the direct relationship between Priority and Objective tables, any filter set on a column from Objective will NOT impact any column selected from Priority table, because Objective is at the end of a Many relationship and filters flow from the one to the many.
However, we have enabled Cross Filter Direction - BOTH on the relationship.
This essentially means filters can flow from Objective to Priority. Just like on the previous example we saw Priority 'A' affecting the Statuses, here we see the Status affecting Priorities. This is due to the bidirectional crossfiltering and it is the intended behavior.
If this behavior is not wanted, bidirectional cross filtering should be turned off. This means switch Cross Filtering to Single.

 

Best Regards,
Qiuyun Yu 

 

 

Idea Statuses