Forum Discussion
Resolver IDynamic 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.
I was able to do by using Segmentation table for dates then use VAR selection.
5 Replies
- Greg_Deckler
Community Champion
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.
- ritanoori
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.
- Greg_Deckler
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.
