Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Dynamic Matrix based on filter selection

Hello Everyone,

I have a matrix where rows are measures and columns are Dates "Month-Year". I want to find a way that when a month is selected in filter. Matrix will show Current Month "Selection", CM-1, CM-3, CM-6 and CM-9. And this need to be dynamically changed along filtering.

Thanks in advance.If Sep 2018 is selected, Matrix needs to show Sep18, Aug18,Jun18, Mar18 and Dec17 in this example.

1 ACCEPTED SOLUTION
Resolver I

I was able to do by using Segmentation table for dates then use VAR selection.

5 REPLIES 5
Super User

Hello @ritanoori, I believe I was able to do what you want with 2 disconnected tables. See Page1, Table8, Table9 and Table10 of attached.

The measure ends up like:

```Measure 5 =
VAR __selectedMonth = MAX('Table9'[Month])
VAR __currentMonth = MAX('Table10'[Month])
VAR __show = IF(MONTH(__currentMonth) <= MONTH(__selectedMonth),TRUE(),FALSE())
VAR __sum = SUMX(FILTER('Table8',[Month]=__currentMonth),[Value])
RETURN
IF(__show,__sum,BLANK())```

So, get the selected month from Table9, which is the slicer. Get the selected month from Table10, which is the disconnected table used for the Columns. Then, determine if a value should be shown (__show). Calculate the value to be shown (__sum) and then return a value or not.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Resolver I

Thanks Greg, in this case I have to create a measure for each period required and that will keep adding measures to my rows which I don't want. I only want to show the four lines and coulmns changed based on selection.

Super User

OK, if you can provide some sample data and expected output that would help. I'm not 100% certain what your setup is in terms of calculations, etc. Are you saying that you have measures in rows? Perhaps a screen shot of how your fields are configured in your matrix.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Resolver I

this is the required output

Yes I have measures in rows and Month-Year coming from date table.

Resolver I

I was able to do by using Segmentation table for dates then use VAR selection.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors