How to change color of font in multi column header in matrix visual

[smohit]

Hi ,

I used matrix visual to create an hourly gantt chart type visual which shows number of hours a paricular job runs . I created sequence numbers from 0 to 24 for a day .Then used conditional formatting to create the chart . I need help in powerbi to conditionally change the font of the sequence numbers 0 to 24 which is coming up on visual in the column . I just need business day and below that it should the values of hours but the column header showing from 0 to 24 is not needed and I want to remove it from the visual. PFA a snippet . Please advise . I used logic used from here as reference

https://www.youtube.com/watch?v=SO4mk1H94OA

[v-shex-msft]

Hi @smohit,

Probably you can try to write a measure to check the range of start time and end time, then you can use if statement to filter on the 24-hour array to only display the data label if they are in your range.

BTW, can you please share some dummy data with raw table structure? It should help us clarify your structure to test coding formula.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[smohit]

Thanks for your reply . Please find below some test data . The objective is to show a continous bar chart across Business Days . There is no requirement of start time . Its assumed job starts at midnight on BD1. I would like to hide the sequence numbers I generated in matrix to map the field values using conditional formatting .
 
The jobs always  complete on BD5 or BD6 .  I am getting desired output but due to the multi column header I use for BD and hour combination for BD 5 , BD6 I am unable to remove the sequence numbers I generated .
 
So for example for first row I would have bar chart showing
1 cell for BD1 , BD2 ,BD3 ,BD4 ( counting 24 hours as 1 cell only in the matrix )
For BD5 and BD 6 it will show 24 hours ( so 24 cells )
 
Data sample :
 
Business Day
Date of completion
End Time
BD6
September 6, 2020
11:15:00 AM
BD5
August 8, 2020
6:00:00 PM
BD5
July 8, 2020
11:30:00 AM
BD5
June 5, 2020
2:00:00 PM
BD5
May 7, 2020
1:26:00 PM
BD5
April 7, 2020
12:36:00 PM
 
Time Lookup I created to use for mapping in matrix : I converted each hour into a sequence number , example 01:00:00 of BD5 would be 1 and for BD6 01:00:00 would add 24 number to mark the next day so BD 6 01:00:00 AM would have sequence number as as 24+1 hour = 25 . This was used so that I could use it as measure in column field .
 
01:00:00
BD5
02:00:00
BD5
03:00:00
BD5
04:00:00
BD5
05:00:00
BD5
06:00:00
BD5
07:00:00
BD5
08:00:00
BD5
09:00:00
BD5
10:00:00
BD5
11:00:00
BD5
12:00:00
BD5
13:00:00
BD5
14:00:00
BD5
15:00:00
BD5
16:00:00
BD5
17:00:00
BD5
18:00:00
BD5
19:00:00
BD5
20:00:00
BD5
21:00:00
BD5
22:00:00
BD5
23:00:00
BD5
01:00:00
BD6
02:00:00
BD6
03:00:00
BD6
04:00:00
BD6
05:00:00
BD6
06:00:00
BD6
07:00:00
BD6
08:00:00
BD6
09:00:00
BD6
10:00:00
BD6
11:00:00
BD6
12:00:00
BD6
13:00:00
BD6
14:00:00
BD6
15:00:00
BD6
16:00:00
BD6
17:00:00
BD6
18:00:00
BD6
19:00:00
BD6
20:00:00
BD6
21:00:00
BD6
22:00:00
BD6
23:00:00
BD6
00:00:00
BD4
00:00:00
BD3
00:00:00
BD2
00:00:00
BD1

[v-shex-msft]

Hi @smohit,

I build a sample to display the working range in matrix based on the time lookup table and raw table records, you can try it if it meets your requirement.

Calculated table of hour ranges:

HourRange = GENERATESERIES(0,23,1)

Masure formula:

BD Count = 
VAR _end =
    CALCULATE (
        MAX ( 'Table'[End Time] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Business Day] IN VALUES ( TimeLookup[Business Day] )
        ),
        VALUES ( 'Table'[Date of completion] )
    )
VAR currBD =
    SELECTEDVALUE ( TimeLookup[Business Day] )
VAR list =
    EXCEPT ( ALL ( TimeLookup[Business Day] ), ALL ( 'Table'[Business Day] ) )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( TimeLookup[Time] ) ) + 0,
        FILTER (
            ALLSELECTED ( TimeLookup ),
            IF (
                currBD IN list,
                HOUR ( TimeLookup[Time] ) IN VALUES ( HourRange[Hour] ),
                HOUR ( TimeLookup[Time] ) IN VALUES ( HourRange[Hour] )
                    && IF ( _end <> 0, HOUR ( TimeLookup[Time] ) <= HOUR ( _end ) )
            )
        ),
        VALUES ( 'TimeLookup'[Business Day] )
    )

Matrix design: raw table 'date' and time lookup table 'business day' to row fields, hour range table 'hour' to column fields, measure to value fields. (conditional formatting: 'font' color 'background' based on measure result)

I also attached the sample pbix file below, you can check on it if you still not so clear about it.

Regards,

Xiaoxin Sheng

[smohit]

I actually need to keep BD1 BD2 , BD3 , BD4, BD5 , BD6 as a continous process in one line rather than in different rows as shown . 

I just need a solution by whcih I can conditionally hide the header of columns which shows the hours in number( 0 to 24) as shown in my diagram in the above post. 

How can we conditionally hide the column headers , basically I just want to hide the values in header column which shows hours in numbers like 0 to 24  etc 

[v-shex-msft]

Hi @smohit,

If you move the 'business day' field to matrix column fields, this measure formula also works. 
In fact, I already check all the matrix visual options but not found any related property allow you to hide or accurately custom the matrix header, perhaps you submit an idea for this requirement.

Regards,

Xiaoxin Sheng

[smohit]

Hello Xiaoxin,

Thank you for creating the visual in a much simpler way than I got . But my main question is that how can I hide the hour column header as shown in your visual too ( the 0 to 24 hours column ) which comes up when we use matrix visual . 

Basically I need the values in blue but I want to hide the 0 to 24 hours that show up on the visual ( so only values should show in matrix like blue coloured boxed , but header for columns from 0 to 24 should somehow hide the values and should not be visible in report. 

PFA the column headers that I want to hide via conditional formatting . ( highlighted in yellow)