Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
When you hover over the column the tooltip will show and filter by the matching date
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
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.
Please don't contain any Confidential Information or Real data in your reply.
Best regards,
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?
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
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
When you hover over the column the tooltip will show and filter by the matching date
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
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. 😀
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |