Drill Through - Keep all filters but Ignore Financial Year Slicer Filter
Hi guys, I using the Drill through function in PowerBi to jump from one tab into the other and all working fine so far and the filters are taking over into the drilled through page, but for one page I would like my slicer filter which are placed on the dashboard as a slicer visual to not apply. I tried through "Edit Interactions" to disable my time series visual to ignore the financial year filter but somehow the drill through filter overwrite that and still filters on financial year. I have financial year as a slicer on all my tabs and I want to use that slicer so i cant take it out from my drill through filter panel on the right side under visuals. I just want for that specific tab that no financial year filter is applied, but its always overwriting it. I tried to included ALL(..) into my DAX calc but didnt work either. Hope any one has a solution..
Display dynamic 'reduction targets' across multi-level hierarchy in column chart
Hi all, I would appreciate some advice on a problem I have been looking at for a while. This is to do with reporting on different sustainability targets. I want to make a clustered column chart that shows both the performance of a company towards in reducing various measures (eg water use) and the reduction target (ie. reduce X by 50%). The different targets have different sub-targets, so I have built a hierarchy. One issue is that the main targets that will be shown in this column chart are at different levels of the hierarchy, I have therefore unpivoted the different levels to create one column called 'Value'. Examples of this (sample data below) are that 'GHG Emissions' and 'Waste' are Level 1 targets that need to be included, 'Heating' is a level 2 target that needs to be included and 'International flights' and 'domestic flights' are level 3 targets to be included in this column chart. I cannot change the hierarchy so that these values are all in the same level unfortunately! This approach works fine and I can see the appropriate target categories. The issue is with the reduction target figure that is also included in the clustered column chart (ie. reduce X by 50%). When I use drill down to see other levels in the hierarchy the target from the first level of the hierarchy propagates through to all lower levels. This is a problem because some of the sub targets have their own reduction target and I would like the chart to change to show the sub targets when drill down is used. Example: Target level 1 is GHG emissions: this has a reduction target of -50% GHG emissions breaks down into a handful of different subcategories like cars, heating etc. 'Heating' is a sub target that falls under GHG emissions as Target level 2 and it has a reduction taregt of -60%. So in this example I would initally like the reduction target column on the chart to show -50%, then when users drill down on that column I want to see 'Heating' to have a reduction target column of -60% (and other Target Level 2 categories to retain -50%) How can I achieve this? I can restructure my data if needed. This is my current set up (Many to One relationship between 'Fact' and 'Hierarchy' on the 'Hierarchy ID' column Columns used in my column chart: X-axis: 'Fact'[Value] Target Hierarchy (this is the name of the hierarchy I built in Power BI) 'Hierarchy'[Targets Level 1] 'Hierarchy'[Targets Level 1] 'Hierarchy'[Targets Level 1] Y-axis: 'Fact'[Reduction Target] (this is a measure I made below) 'Fact'[Performance] Sample of the hierarchy table: Targets Level 1 Targets Level 2 Targets Level 3 Hierarchy Key GHG Emissions Heating Gas 1 GHG Emissions Heating Electric 2 GHG Emissions Cars Petrol Cars 3 GHG Emissions Cars Electric Cars 4 GHG Emissions Flights Domestic Flights 5 GHG Emissions Flights International Flights 6 Waste Waste Incinerated Waste 7 Waste Waste Food Waste 8 Waste Waste Landfill Waste 9 Water Water Used 10 Sample of the Fact table: Hierarchy Key Attribute Value Reduction Target 1 Reduction Target 2 Reduction Target 3 1 Targets Level 1 GHG Emissions -0.50 -0.60 -0.60 1 Targets Level 2 Heating -0.50 -0.60 -0.60 1 Targets Level 3 Gas -0.50 -0.60 -0.60 2 Targets Level 1 GHG Emissions -0.50 -0.60 -0.60 2 Targets Level 2 Heating -0.50 -0.60 -0.60 2 Targets Level 3 Electric -0.50 -0.60 -0.60 3 Targets Level 1 GHG Emissions -0.50 -0.50 -0.50 3 Targets Level 2 Cars -0.50 -0.50 -0.50 3 Targets Level 3 Petrol Cars -0.50 -0.50 -0.50 So far all I have thought of is using a measure like the below, but this does not work (obviously): Reduction Target = VAR CurrentLevel = SELECTEDVALUE('Fact'[Value]) VAR ReductionTarget1 = MAX('Fact'[Reduction Target 1]) VAR ReductionTarget2 = MAX('Fact'[Reduction Target 2]) VAR ReductionTarget3 = MAX('Fact'[Reduction Target 3]) RETURN SWITCH( TRUE(), CurrentLevel IN VALUES('Hierarchy'[Targets Level 1]), ReductionTarget1, CurrentLevel IN VALUES('Hierarchy'[Targets Level 2]), ReductionTarget2, CurrentLevel IN VALUES('Hierarchy'[Targets Level 3]), ReductionTarget3, BLANK() ) When I use the above measure the reduction target is shown correctly on the inital view (when Fact[Value] is what is seen by the user). But when I drill down to see the hierarchy the target from the first level propogates through. Any solutions welcome, it may be that making a measure like I have tried to do is the wrong approach!DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days
Hi all...so I have a chart, let's call it chart A. that chart is filtered on today's date because it's used as a monitoring chart for infrastructure incidents. I have chart B which is supposed to show past 7 days. Ideally, when something shows up in chart A that's bad, as it means it's a problem ONLY today. I have a filter set to incident shows up in table if remaining disk space is <20%. So let's say Server A shows up in Chart A. Below this, in Chart B, I want to see that server's prior 7 day disk usage. It does not work when I have Chart B set to relative date. In every case, when I click on a server name in Chart A, all it shows me in Chart B is today's data. I want to be able to see results (filtered on today's date) from Chart A and then drill in for values in Chart A to today's date -7 days for Chart B. Literally everything I have tried ends up in Chart B as only showing data for today's date. the other issue is I have a parent child hirearchy in 'Server', which has many child 'Disk Drives'. Violations that show up in Chart A are based on the storage space for 'Disk Drives', on today's date (Chart A). I want to be able to click on the Hostname field, and have ALL drives for that server show me today -7 in Chart B (the drilled in chart). Chart A (based on Nov 8th) Chart B ( Based on Nov 1-7th. these are separate values, used for illustrative not supposed to match) So Server 'A' has 5 disk drives. Today, one of them went to 0% free. Only for today was drive V in violation. I want to have Chart B show me 7 day history for ALL of server A's 5 drives. Thanks
Drill Through in Parent Child Hierarchies
Hi all - I have table with parent-child ids along with Levels. One of requirement is table to see Parent information on the same page when User click on ParentId column in table chart i.e Clicking On ParentId should display information about it. I'm not sure If I can achieve it on same page hence I tried with drill through approach by cloning the exisitng page but It's not working. Please suggest if there are any other approaches to achieve the functionality. Also, Is there option to update Slicer based on Selected Column in Table Chart? Example: Data: Clone the Page and Enable Drill Through: Verify Drill through Option on Summary Page: Clicking on Drill Through gives the data for ParentId = '0728b2f0' but I'm looking for ParentId information i.e PipelineId = ParentId(0728b2f0). Please assit Thanks, AbhiramUsing ISSELECTEDMEASURE to navigate to two different drill through pages
Hi, I have a main page for the report and two for drillthrough. I am using the following measure on the action of a button to navigate to different drill through pages if I select a visual with one measure or if I select another measure on another visual. Drillthrough filter = var page4 = ISSELECTEDMEASURE([Total Open Risks], [Mitigated Risks]) var result = IF(page4,"Page 4", "Page 5") return result What happens is that it works with only one measure, enabling the drillthrough to only one page. For example if I select the visual with the "Mitigated Risk" measure, it brings me to Page 5. If I select "Total Open Risks" the button to drill through stays disabled. I have also tried the following: Drillthrough filter = IF( ISSELECTEDMEASURE([Total Open Risks])," Page 4", "Page 5") The output is the same, it navigates to Page 5 if I select the visual with the measure "Mitigated Risks", if I select "Total Open Risks", the button stays disabled. Another solution I have tried is: Drillthrough filter = IF( ISSELECTEDMEASURE([Total Open Risks]), IF(ISSELECTEDMEASURE([Mitigated Risks])," Page 5"), "Page 4") What happens is that if I select "Total Open Risks", the buttonenables and I can drill through to Page 4, if I select "Mitigated Risks", the button enables, but it brings me to Page 4 instead of Page 5. I have also used the following: switch = SWITCH(TRUE(), ISSELECTEDMEASURE([Total Open Risks]), "Page 4", ISSELECTEDMEASURE([Mitigated Risks]), "Page 5", "") This measure won't enabled the button for drill through with both measure. Any help on how to make this measure work properly is really appreaciated. I use two simple bar charts with Owner on the X axes and on the Y axes one of the two measures below. Mitigated Risks = CALCULATE(COUNT('Risk'[Mitigated]),'Risk'[Mitigated]="Mitigated", USERELATIONSHIP('Calendar'[Date],'Risk'[Mitigation Date]))+0 Open Risks = CALCULATE(COUNTx(FILTER('Risk','Risk'[Creation Date]<= max('Calendar'[Date]) && (ISBLANK('Risk'[Mitigation Date]) || 'Risk'[Mitigation Date]> max('Calendar'[Date]))),[Risk ID]),CROSSFILTER('Calendar'[Date],'Risk'[Creation Date],None)) Risk id Owner Creation Date Mitigation Date Mitigated description 1 owner 1 30/06/2022 31/12/2022 Mitigated descr1 2 owner 2 30/06/2022 Open descr2 3 owner 3 30/06/2022 31/12/2022 Mitigated descr3 4 owner 4 31/12/2022 30/06/2023 Mitigated descr4 5 owner 5 31/12/2022 30/06/2023 Mitigated descr5 6 owner 6 31/12/2022 Open descr6 7 owner 7 31/12/2022 Open descr7 8 owner 8 30/06/2023 Open descr8
