Forum Discussion
Static matrix Column Header (without slicer impact)
Hi Team,
My Date table is joined with Sales Table.Sales Table contains one dimension field which is also slicer for the report page.I have created matrix with Quarter(Column Header from Date table) wise Sales Amount(Value).Now customer requirment is if they select any value from that dimension slicer,Quarter header should show all values(remain static) even that quarter date field is not associated with that dimension value.
Now when I select value from dimension slicer which is coming from Fact table,quarter value is also changing & showing only related quarters.Filter propogation is one to many from date to Sales Fact table.Is there any way to show all quarter values irrespective of selection from that dimension slicer.I tried using function ALL but it didnt work.Any help is really appreciated.
Thanks,
5 Replies
Hi shashankp1 ,
According to my understand, you want to keep the row header in Matrix but display dynamic values based on slicer ,right?
Since there is a relationship between Sales and Date tables , you should create a new table with Quarter columnfor slicer. Then use the following formula:
Measure = VAR _sele = SELECTEDVALUE ( Sales[ID] ) VAR _quarter = CALCULATE ( MAX ( 'Date'[Quarter] ), FILTER ( 'Date', 'Date'[ID] = _sele ) ) RETURN IF ( MAX ( 'forSlicer'[Quarter] ) = _quarter, SUM ( Sales[Amount] ), BLANK () )
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qinhi shashankp1 - Have you tried to edit interactions between the slicer and the matrix visual so changing the Qaurter slicer does not impact the matrix visual
You can refer to the Microsoft documentation on the same:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions
Please mark the above comment as a solution to help others find it more quickly. Also please provide a 👍 if my comment helped with solving your issue. Thanks!
Hi Sumath.Thanks for your reply but I cannot do edit interaction as it will not change Sales Amount.I want sales amount should change as per slicer but header should remain static & will show blank Sales value where related value is not present.Hope you got my problem.
