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
LuckyLuke
Frequent Visitor

How can I control what is in the Data Point Table?

Hi,

 

I have some PBI reports that were initially developed with a flat data model (import from a couple of CSVs).

Thanks to some great help from these forums I have re-developed the data model using a star schema and added a custom date table, and I'm using measures because there a multiple joins to the fact table.  The reports are working great, have way more features that before and I can show the data in new ways.

 

However there is a wrinkle!  In the old reports the users loved that they could right click on a part of a visual, choose see data point table and see the case ids of the cases being reported on (e.g. there was no activity on then in 4-8 weeks) they would then take these case ids and look then up in our case handling system.  With the new model this is no longer working because it's all linked to the custom date table and using measures, they either get no option to see a data point table, or they just get the relevant row from the date table with the count of cases rather than a row for each case with the case id visible.

 

I need to work this ability to view and export the case id's back into the reports built on the new model.  Could anyone point in the direction of how to ensure that "show data point as a table" is available as an option (is this possible with measures) when right clicking, and it that it gives the information from the right tables?

 

If this is not possible when using measures in a visual, how else can I populate the visual so that it uses a particular relationship?

 

 

TIA

1 ACCEPTED SOLUTION

If I'm understanding correctly you have 3 options

 

1) add a table on the same page as the chart. You can then filter the table by clicking the column/scatter data point

case1.PNG

2) Add a custom tooltip.

Create a new tooltip page and use the date as a tooltip filter.

https://docs.microsoft.com/en-us/power-bi/desktop-tooltips

tooltip.PNG

When you hover over the column the tooltip will show and filter by the matching date

 

case2.PNG

 

3) Drillthrough.

Similar to the tooltip you can add a new page and set the drillthrough filter to the date. You then get a right mouse option to drill through from your column chart.

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

case3.PNG

View solution in original post

6 REPLIES 6
v-lid-msft
Community Support
Community Support

Hi @LuckyLuke ,

 

Sorry for that, We cannot understand your requirement clearly, Do you mean the see record feature in the Scatter Visual?  Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.

 

10.jpg11.jpg

 

 

Please don't contain any Confidential Information or Real data in your reply.

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi  @v-lid-msft ,

 

The visuals I'm using is a column chart but yes it is the same as the see record feature in PBI Desktop.  In the last few months this has been renamed on the web app to "show data points as a table".

 

I will try to create a mock-up with sample data but this will take some time so I will  describe the tables here.

Table : Calendar

Fields: Date, Weeks Since, (various other typical calculations for a date table)

 

Table: Case

Fields: Case Key, Date Received, Date Closed, Date Transferred to Dept, Date of Last Action, (various other dimension keys)

 

There are various other dimension tables but they aren't relevant to this issue.

 

I have inactive relationships from all 4 date fields in the Case table to the Calendar table.

I have 4 measures on the Calendar table that use USERELATIONSHIP. E.g.

 

Sample Measure:

Complaints Closed = CALCULATE(COUNT(Case[Case Key]),USERELATIONSHIP('Calendar'[Date],Case[Date Closed]))

 

In the Column chart visual I have the Week Since as  the Axis, and the measure as the Value.  This works fine for the visual itself and the chart looks as expected however the users need to be able to right click on a bar in the chart and get a list of the Case Keys for that cohort of cases (e.g. cases where there the last action was 10 weeks ago).   See records in PBI Desktop (or show data points as a table in the web app) would normally do this, but because I am using the measures I don't get that option.

 

Due to the 4 inactive relationships I don’t know how to filter the data without using the measures and USERELATIONSHIP.  Is there another way to do it so that “view records” is available?

 

I hope this is clearer?

stretcharm
Memorable Member
Memorable Member

Can you share a sample pbix or the star schema model?

If you have not aggregated your data before loading then you will still be able to see the detailed caseids.

 

I often provide lists of detailed data in a table or drill through to another page with the details.

 

Hi @stretcharm ,

 

Thanks for your reply.

I am not aggregrating the data, and I have also done it before but it seems when you use a measure that it doesn't work.

 

Please see my reply to v-lid-msft for more detail on the schema and why I'm using measures.

 

Perhaps there is an alternative to using the measures?

 

If I'm understanding correctly you have 3 options

 

1) add a table on the same page as the chart. You can then filter the table by clicking the column/scatter data point

case1.PNG

2) Add a custom tooltip.

Create a new tooltip page and use the date as a tooltip filter.

https://docs.microsoft.com/en-us/power-bi/desktop-tooltips

tooltip.PNG

When you hover over the column the tooltip will show and filter by the matching date

 

case2.PNG

 

3) Drillthrough.

Similar to the tooltip you can add a new page and set the drillthrough filter to the date. You then get a right mouse option to drill through from your column chart.

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

case3.PNG

Hi @stretcharm ,

 

Thank you for your reply and great suggestions!

 

It took me a little while to figure out that to do it this way I need to include the measure in the table to get the inactive relationship to be used, but once I figured that out it works great. 

 

I need to have multiple drillthrough forms with the measure I want included on each but as the forms can all me named the same this is invisible to the user.

 

Now I just need to figure out how I can hide the measure as it's just showing 1 for each row.

 

Many thanks for you help.  😀

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.