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

Reply
ritanoori
Resolver I
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.If Sep 2018 is selected, Matrix needs to show Sep18, Aug18,Jun18, Mar18 and Dec17 in this example.

1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

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. 


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

this is the required outputthis is the required output

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors