Imitate gantt chart using Matrix visual.

[Konrad909090]

Hello experts,
I am trying to build a solution to imitate a gantt chart using matrix visualisation.
The gantt is to display the duration of the project and the colour depending on what stage the project is in.

The problem is that I don't know how to display the right colour when a team name is not developed to see the projects that this team has.
What I want to achieve is e.g. one colour for all the teams when using the plus sign to not expand the rest of the rows.
At the moment, when I use the + to expand the rest of the rows, the team for which I do not expand shows only 1. I would like to replace this with one colour.
It seems to me that the problem lies in the part that is responsible for the colouring of the font and background in the visualisation, which I use in the section ‘Call elements’ -> ‘Backgound colour’ and ‘Font colour’.

Attached are screenshots of the model, measures and a link to the dummy report.
I would appreciate your help 🙂

Link to dummy report 

[Ritaf1983]

Hi @Konrad909090 
if i understood you correctly and you are looking for the option to apply conditional formatting on the row header

then unfortunately we don't have this functionality, the conditional formatting cam be applied only with the "values" section.
There is an idea about the issue, you can vote for it here :
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=81d13d9e-0b12-ee11-a81c-000d3a0ec312

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Konrad909090]

Hi @Ritaf1983 ,

thank you for answer.

I would like to hide the values when a row is not expanded, or set one colour for all collapsed rows.
After expansion, I want them to look as they currently do.
Blank

Or with color

Best regards 
Konrad

[Ritaf1983]

Hi @Konrad909090 
You can manipulate the values that are shown according to the hierarchy level using the function is in scope.
You didn't attach data to work with , so I will show how it works with my example :

This is my matrix 
Country << category on rows.
Year<< month on columns.
Total sales on values 

To keep a country level blank I can create a measure :

Blanks_ = if(ISINSCOPE('Products'[Category]),[total_sales],"")

The formula says that if I am not on the category level show total sales and on the country level keep it blank.
The result :

the pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Konrad909090]

HI @Ritaf1983 ,

Thanks again for the reply and the solution provided.
I used the ISINSCOPE function. Unfortunately in my case all the values disappear.
I think I am following the logic given in your pbix file but I am still not able to achieve what I want.
I have attached a link to the file in the first post. I can't upload the file directly in the message, I get a message that these types of files are not supported on this form even though you uploaded yours.
So I am providing a link to google drive with my file again.
If you have the time and inclination, I would appreciate it if you would take a look at it.

https://drive.google.com/file/d/1YT8YZAa2KyeFfH0njMCwEvxEHc-If-SY/view?usp=drive_link

[Ritaf1983]

Unfortunately, there is no access to the file ...you need to share it with the option "everybody who has a link"

[Konrad909090]

Sorry, typical mistake. The link should now work. I have also accepted your request.

https://drive.google.com/file/d/1YT8YZAa2KyeFfH0njMCwEvxEHc-If-SY/view?usp=sharing

[Ritaf1983]

Hi @Konrad909090 
I update your formula to 

Gantt Value =

VAR ProjectStart = MIN('Projects'[Start date])

VAR ProjectEnd = MAX('Projects'[End date])

var result=

    IF (

        AND

            (

                MAX('Calendar'[Date]) >= ProjectStart,

                MIN('Calendar'[Date]) <= ProjectEnd

            ),

            1,

            BLANK()

    )

    RETURN

    if(ISINSCOPE(Projects[Project name]),result,"")

And it seems that there is a result as you wanted :

The updated pbix is attached

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Konrad909090]

Thank you for your help.
It's strange in your version of the report I can't see the colours when the teams are rolled up.

Additionally the colour shows for all years on your screenshot.

In addition, the team ‘Yellow’ shows but I can eliminate it after by applying a filter.

However, this is more or less what I want to achieve. I just don't know where the problem now lies with the display of colours when the teams are collapsed.

[Ritaf1983]

Hi @Konrad909090 
Is this a wanted result?

If yes, than all manipulations with isinscope are a part of conditional formatting.
For a background :

Colors =

IF( [Gantt Value] <> 0 && ISINSCOPE(Projects[Project name]),

SWITCH(

    MAX('Projects'[Stage number]),

    4,"#3982A7",

    3,"#B832AC",

    2,"#881176",

    1,"#BA545B",

    0,"#DC7455"

),

 IF(isinscope('Team'[Team]),"#ffdbbb"

    )

)

For a font color:

Font color =

if(ISINSCOPE('Projects'[Project name]),"black","#ffdbbb")

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Konrad909090]

Hi @Ritaf1983

Thank you very much for your continued attempts.
We've almost got it, but when rolled out, one team shows the colours and the other only shows ‘1’.

[Ritaf1983 · Accepted solution]

Hi @Konrad909090 
Modify the conditional formatting for "Values and totals"

+ if you want the colors of the top row stay when expended , switch on the sub-total per team level:

Result :

Modified pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Konrad909090]

Hi @Ritaf1983 

Thank you very much, this is exactly what I wanted to achieve.
You have saved my day today 😄
THANK YOU VERY MUCH AND HAVE A NICE DAY 😄

Regards
Konrad

[Ritaf1983]

Happy to help 🙂