Forum Discussion
Power BI drill through page
Hi,
I have a drill through report which drill through
When I click on one of the title, it takes me to drill through page (DTR). I have activated Keep all my filters and therefore it displays for the selected month and year and title.
The title, reference, Asset Management OBjective and target comes from Table A and Value is in Table B. Both tables are joined by ConnectorID.
However, I need another visual on the DTR page. I would also like to see a visual which shows last 6 months of data for the selected title. (This includes removing all filters from Table A except Title).
Also, would it be possible for thisthe table above which displays filtered data to display any selected value from the last 6 months data.
I have used the below measure to remove all filters for the table. But, unfortunately this did not work.
Hope this makes sense
Hi Anonymous ,
Thank you for reaching out to the Microsoft Community Forum.
1. Creating a Last 6-Month Visual (Keeping Title Only):
You can use a measure or a calculated table to show the last 6 months of data, based on the selected Title only. Here’s a pattern for a measure you can use in a visual:
Measure_Last6Months =
VAR SelectedTitle = SELECTEDVALUE('TableA'[Title])
VAR MaxDate =
CALCULATE(
MAX('TableB'[Date]),
ALL('TableB')
)
VAR StartDate = EDATE(MaxDate, -5)RETURN
CALCULATE(
SUM('TableB'[Value]),
'TableB'[Date] >= StartDate &&
'TableB'[Date] <= MaxDate,
'TableA'[Title] = SelectedTitle,
REMOVEFILTERS('TableA'[Month], 'TableA'[Year]) -- optional: removes other filters
)Note: You can plot this in a line chart or table using 'TableB'[Date] on the axis and this measure as the value. Make sure your Date column is in a format Power BI recognizes as date.
2. Reflect Value from Last 6 Months in the Main Visual:
To include values from the last 6 months in the main table while still showing the selected month’s context, you’ll need a separate measure that ignores date filters except the Title and calculates for that 6-month window.
Here’s a revised version of your original Measure 7:
Measure_7_Last6Months =
VAR SelectedTitle = SELECTEDVALUE('TableA'[Title])
VAR MaxDate =
CALCULATE(
MAX('TableB'[Date]),
REMOVEFILTERS('TableB')
)
VAR StartDate = EDATE(MaxDate, -5)
VAR FilteredValues =
CALCULATETABLE(
VALUES('TableB'[Value]),
'TableB'[Date] >= StartDate &&
'TableB'[Date] <= MaxDate,
'TableA'[Title] = SelectedTitle,
REMOVEFILTERS('TableA'[Year], 'TableA'[Month])
)RETURN
IF(
NOT ISBLANK(SelectedTitle),
CONCATENATEX(
FilteredValues,
'TableB'[Value],
", "
)
)Note: This will concatenate the values from the last 6 months, ignoring all filters except Title.
Note: Replace 'TableA' and 'TableB' with your actual table names (Testing Service Performance Measure - CLOS and related). Ensure relationships are correctly established on ConnectorID. Ensure Date is present and used consistently in 'TableB' for the 6-month logic.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
4 Replies
v-dineshyaCommunity Support
Hi Anonymous ,
Thank you for reaching out to the Microsoft Community Forum.
1. Creating a Last 6-Month Visual (Keeping Title Only):
You can use a measure or a calculated table to show the last 6 months of data, based on the selected Title only. Here’s a pattern for a measure you can use in a visual:
Measure_Last6Months =
VAR SelectedTitle = SELECTEDVALUE('TableA'[Title])
VAR MaxDate =
CALCULATE(
MAX('TableB'[Date]),
ALL('TableB')
)
VAR StartDate = EDATE(MaxDate, -5)RETURN
CALCULATE(
SUM('TableB'[Value]),
'TableB'[Date] >= StartDate &&
'TableB'[Date] <= MaxDate,
'TableA'[Title] = SelectedTitle,
REMOVEFILTERS('TableA'[Month], 'TableA'[Year]) -- optional: removes other filters
)Note: You can plot this in a line chart or table using 'TableB'[Date] on the axis and this measure as the value. Make sure your Date column is in a format Power BI recognizes as date.
2. Reflect Value from Last 6 Months in the Main Visual:
To include values from the last 6 months in the main table while still showing the selected month’s context, you’ll need a separate measure that ignores date filters except the Title and calculates for that 6-month window.
Here’s a revised version of your original Measure 7:
Measure_7_Last6Months =
VAR SelectedTitle = SELECTEDVALUE('TableA'[Title])
VAR MaxDate =
CALCULATE(
MAX('TableB'[Date]),
REMOVEFILTERS('TableB')
)
VAR StartDate = EDATE(MaxDate, -5)
VAR FilteredValues =
CALCULATETABLE(
VALUES('TableB'[Value]),
'TableB'[Date] >= StartDate &&
'TableB'[Date] <= MaxDate,
'TableA'[Title] = SelectedTitle,
REMOVEFILTERS('TableA'[Year], 'TableA'[Month])
)RETURN
IF(
NOT ISBLANK(SelectedTitle),
CONCATENATEX(
FilteredValues,
'TableB'[Value],
", "
)
)Note: This will concatenate the values from the last 6 months, ignoring all filters except Title.
Note: Replace 'TableA' and 'TableB' with your actual table names (Testing Service Performance Measure - CLOS and related). Ensure relationships are correctly established on ConnectorID. Ensure Date is present and used consistently in 'TableB' for the 6-month logic.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you- v-dineshya
Hi Anonymous ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you- v-dineshya
Hi @arak ,
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you