We have a matrix table which shows 2 measures, with Store Names in the Columns field going across the report. To make the report more readable we would like to create our own banded column shading, as in the mock up below (taken from the Contoso dataset using Continent instead of Store Name):
I thought this could be achieved using Background Colour Conditional Formatting, if I could assign an alternating value to each Store, in the selected dataset, eg: 1, 0, 1, 0 etc
I know that if I can find out how to return a derived sequential ‘row’ number for my stores, then using MOD(row_number, 2) will return my alternating 1,0,1,0.
However I am struggling with the DAX to return this sequential number? The code would need to respect any filters applied by the user, so we could have 3 sets of columns (Store Names) or 20 sets of columns.
The other complication, is that our ‘Stores’ table has examples where we have more than one record per Store Name, due what we call ‘Child Stores’, so I think the DAX would first need to summarise our Stores table by Store Name, so we have 1 distinct value per Store Name and then work out this ‘row_number’
Any thoughts really appreciated!