Unexpected result with table visualisation and userrelationship

[aceeer]

Hi,

I've started working with PowerBI for a few weeks now and I can't get my head around why my table visual isn't filtering as expected.

I have 2 tables: Table1 and calendar, with several inactive relationships between the two as many-to-one.

I've created a measure for my bar chart ("Issues" field on-axis and Measure on Values) and all is working well, but when I create a visualization table and add several fields to it, whenever I filter the bar chart the number of records in the chart and the table does not align. It seems like I am missing an additional filter somewhere.

Measure: 

Volume Expected Completed = CALCULATE(COUNTROWS('Table1'),USERELATIONSHIP('Table1'[End Date],Calendar[Date]), FILTER('Table1','Table1'[Expected_Status] = "Completed"))

Image of the problem: Image

Thanks

[selimovd]

Hey @aceeer ,

to answer this question you have to give more information.

Best if you could upload the file, otherwise show the measures, a screenshot of the relationship and which measures are used for the visuals.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[v-lionel-msft]

Hi @aceeer ,

If you add the Y-axis field of the histogram to the table visual, you may be able to get the correct number of rows.

Best regards,
Lionel Chen

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

[aceeer]

Hi @selimovd, 

Thanks for the reply, I've attached an additional image of the chart and the model.

Not sure why the image isn't getting attached in the body of the message but I've added them below:

Image 1 

Image 2 

Basically, what I'm trying to achieve is to do a drill through on the bar chart which then shows the corresponding 4 records in the table view. But as seen in Image 1, when I click on one of the bars (e.g. "Missing PE...") it shows 4 records, but in the table below it is showing 6. 

It looks like the table is showing all records with Issues = "Missing PE..." but I expect it to show all records where the condition is Issues = "Missing PE..." and Expected_status = "Completed". 

[selimovd]

Hey @aceeer ,

it's still hard to get much information from this two screenshots.

Did you realize that the relationships between the Calendar table and Resource are all inactive? Why is that? Did you do the relationships only within a measure?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[aceeer]

Thanks for the reply @selimovd 

---

Did you realize that the relationships between the Calendar table and Resource are all inactive? Why is that? Did you do the relationships only within a measure?

---

Yes, the relationships are only in the measures. Am I doing something wrong or missing something? 

Each of the relationships I have created a measure to perform some computation that involves filtering such as: 

Volume Expected Completed =

CALCULATE(COUNTROWS('Resource'),USERELATIONSHIP('Resource'[End Date],Calendar[Date]), FILTER('Resource','Resource'[Expected_Status] = "Completed"))

and 

M Cancelled = CALCULATE(COUNTROWS('Resource'),USERELATIONSHIP('Resource'[Cancelled],Calendar[Date]), FILTER('Resource','Resource'[Status] = "Cancelled"))

The measures are then used in 2 bar charts. 1st measure:  Volume Expected Completed for Issues bar chart, and 2nd measure M Cancelled for failures chart.

See image: Image3  

Here is the report view with the 2 bar charts and the table visual. 

Image4 

I want to be able to click on either of the bar charts and on any category so that the table displays the corresponding number of rows as shown in the chart. 

So clicking on Carrier issues in the issues bar chart should in return display 30 rows in the table. And Clicking Missing PE... in the chart should return 4 rows but as seen it is showing 6. 

Thanks 

[selimovd]

Hey @aceeer ,

no the measures look fine for me.

Can be that the cross filtering doesn't work when you didn't set a default relationship.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[aceeer]

Hi @selimovd 

I tried having one of the relationships active, but it didn't provide the expected results. 

I was thinking I would need to include the ID field in the measure so it would somehow filter through but not sure it would even work. 

In the table visual, you can see the Expected_Status is not getting filtered as per the measure because it includes rows where the Expected_Status = "Unsuccessful" and "Successful with issues". The only thing that seems to be filtered on is the Issue or Failure selection on the bar chart. 

Volume Expected Completed =

CALCULATE(COUNTROWS('Resource'),USERELATIONSHIP('Resource'[End Date],Calendar[Date]), FILTER('Resource','Resource'[Expected_Status] = "Unsuccessful"))

If I can't think of a solution I'll probably go back to using role-playing dimensions with several date tables. Appreciate the help.