Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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.
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.
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:
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".
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?
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.
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
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.
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.
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |