Power bi matrix showing only selected period instead of full range

[Simonn97_PBI]

I have a problem to solve that seems straightforward. I have a standard star schema data model with a Fact table and a one-to-many relationship to DimDate. I want my matrix to show values from P1 through the selected period—for example, if I single-select P8 on the Period slicer, I should see P1–P8. Right now, however, I only see P8.

I wrote a measure that I thought should work, but it still only shows P8. If I write a similar measure that modifies the filter context on the Fact table using the DateKey column, it does return multiple periods (although not all, because I have FY and FinancialPeriod filters applied on DimDate).

What might be causing this to not work as intended?

Total AC =

VAR _selectedPeriod = MAX(dimDate[PeriodSort])

RETURN

CALCULATE(

    SUM('FactP&L'[Value]),

    FILTER(

        ALL(dimDate),

        dimDate[PeriodSort] <= _selectedPeriod

    )

)

[v-karpurapud]

Hi @Simonn97_PBI 

At this time, it's not possible to avoid using a disconnected table, as @Sergii24  correctly mentioned in the earlier response. When a single-select slicer is based on a connected field like DimDate[Period], Power BI applies that selection as a strict filter throughout the data model. As a result, only the selected period (such as P8) is shown in the matrix, and earlier periods (P1 to P7) are not displayed, even if your DAX measure uses ALL(dimDate) with a PeriodSort condition. This happens because the matrix visual still follows the filter context from the dimension table, so unselected periods won't appear unless they're already in the visual. The best way to handle this is by using a disconnected table for the period slicer. This breaks the direct filter relationship and lets your DAX logic return cumulative values (like P1 to P8) without limiting the matrix’s rows. While you may need to update measures in several visuals, this is currently the only effective and supported approach. Power BI does not offer another method to achieve this with only connected slicers.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[KNP]

@Simonn97_PBI,

Not exactly what you're asking for, but can you use a slicer set to 'Before'?

It will need to be a number, not a string, but thought I'd float the idea just in case the simple option works.

[Simonn97_PBI]

I simply want to see all the numbers up to period 7 for example, at the moment I can only see the numbers for period 7 without p1-p6. The single slicer option as shown in the screenshot must remain

[v-karpurapud · Accepted solution]

Hi @Simonn97_PBI

Welcome to the Microsoft Fabric Community Forum.
 

Regarding your question about displaying only the selected period in the matrix visual, I have created a sample .pbix file to show a possible approach. While the sample may not exactly match your dataset, it demonstrates how you can use a disconnected slicer table, calculated columns, and DAX measures to display all periods from P1 up to the selected period.
 

I've attached a screenshot and the .pbix file for you to review. Please check if this solution works for your scenario or let us know if you need further adjustments to suit your data model.

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
 

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.


 

[Simonn97_PBI]

Thanks for trying to solve my problem. However, I’m aware of how a disconnected table can be used to address this issue. The problem is that I’d like to avoid that, because there are several other visuals in the report where I’d also have to modify the measures.

Is there a way in DAX to achieve the effect you presented without using a disconnected table?

[Sergii24]

Hello @Simonn97_PBI, the answer to your problem is to use disconnected tables as it has been proposed by @v-karpurapud.

Let's explore why this is the case. You may recall that you can't use measures in the matrix rows and columns sections. That's because in those sections you define the elements you want to use for "slicing and dicing" your data. And what elements can be used for such an activity? Correct - the physical elements of your data model: columns and calculated columns. Therefore, to "see" periods from P1 to P8 when you make a single filter selection, one of two things should happen: your filter is not connected to a visual, but in that case, you get all P periods. Or you use a disconnected table and then, using DAX, define what should happen to a calculation. As a result, Power BI will "show" you relevant periods, not beucase you somehow selected P1-P8, bit because your DAX calculation provides results for those periods (if you select periods in columns and click on "show rows without values" you'd get all P periods again, eventhough only part of them will have a resulting values.

You can't use a sigle selct filter to show data "all before selected", because when you select P8, it's filtering the dimensional table with a single value in that column and propagates filters acconrdingly. So Power BI doesn't understand that when you do "single select" you actually want a "mutli select". I hope it brings some clarity on your question! 🙂

Good luck with your project!