Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

dynamic conditinal formatting based on mtrix condition

Hi there,

When you have a martix with Actuals and Budget columns the precentages and color coding make sense when the matrix colapsed but when you expand the matrix it dosen't make sense to have color coding because you can't compare line item to the overall budget (Let say office supply budget for the month is 1000 but when I expand I have 10 items 100 each so I can't compare line item to the budget)

My question how to make the color coding appear when the matrix is collapse and invisible when it expand?

Thanks,

Oded Dror

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi there,

 

This one works and I did conditional formatting based on that

 

Formatting =
VAR Percentage = [Actual Vs Budget]
VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||
    Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))
RETURN
If(CheckExpand = TRUE(),
   Percentage,0)
Thanks
Oded Dror

View solution in original post

Hi @Anonymous ,

 

Glad I could give some pointers to get the solution don't forget to mark your result as the answer for this post to help others.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

13 REPLIES 13
MFelix
Super User
Super User

Hi @Anonymous,

You should try something like

Conditional = IF(HASONEFILTER( TABLE[Column]) ; 0 ;1)

Then make the conditional formatting based on the 1.

You can also replace the 1 by the hex coding and then you just need to set it in the conditional formatting.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi MFlix,

 

I tried that but it still show conditional formatting when the matrix is expanded.

One thing my conditional colun is based on other column (Precent value) and adding another column make it impossible to achive.

If it was ability to do like SSRS when you can change the background colore based on condition with IF satement then your solution was apply.

 

Thanks,

Oded Dror

Hi @Anonymous ,

 

I made a small example with the following setup:

 

Cat SubCat Value
A A 100
A B 200
B A 100
B B 100
C A 20
C B 50

 

Created the following measure:

Formatting = IF(NOT(HASONEVALUE('Table'[SubCat]) );"red")

Then made the condittional formating on values:

conditionl.png

 

Two remarks:

  • Use the NOT so that the expanded would not need to have the hex coding
  • You can change the "Red" by the HEX code something like "#FF0000"

 

Be aware that when you expand one of the categories on the matrix table the condittional formatting will not aplly to any subtotals (this is behaviour on PBI not error).

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi there,

 

You are checking the condition of the matrix if is expaned or collapsed that's one thing

The second thing is if it collapsed (then check) and subToal is over Budget below 10% then yellow over 10% red

And when the matrix is expanded don't show the background color, most likely I need to add calculate value too
to the IF statement, can you incorporate multiple IF statements and show me how to achive that? I need help on that.

 

Thanks,

Oded dror

 

Hi @Anonymous ,

 

You can use a SWITCH function that evaluates an expression against a list of values and returns one of multiple possible result expressions.

 

Formatting =
VAR Percentage =
    ( SUM ( 'Table'[Value] ) / SUM ( 'Table'[Target] ) ) - 1
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'Table'[SubCat] ) )
RETURN
    SWITCH (
        TRUE ();
        CheckExpand
            && Percentage < 0,1; "Yellow";
        CheckExpand
            && Percentage > 0,1; "Red"
    )

You can change the Percentage formula by the one you think is more suitable to your calculations.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi there,

this is my calculation - no error there but when I put this measure in my marrix I'm getting this error (see below)

Formatting =
VAR Percentage =
   Divide(((Sum('ActualVsBudget'[Activity_YTD]) - Sum(ActualVsBudget[BUD_YTD]))) , Sum(ActualVsBudget[BUD_YTD]) )
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'GLedger'[Account]) )
RETURN
    SWITCH (
        TRUE(),
        CheckExpand
            && Percentage < 0,0.1, "Yellow",
        CheckExpand
            && Percentage > 0,0.1, "Red"
    )
 

Error Message:
MdxScript(Model) (34, 31) Calculation error in measure 'GLedger'[Formatting]:
Function 'SWITCH' does not support comparing values of type True/False with values of type Number.
Consider using the VALUE or FORMAT function to convert one of the values.
 
Thanks,
Oded Dror
Anonymous
Not applicable

Hi there,

 

I modify the measure and got it to work but it still showing flags when th ematrix is expandad 

 

Formatting =
VAR Percentage = [Actual Vs Budget]
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'GLedger'[DD]) )
RETURN
    SWITCH (
        TRUE(),
        CheckExpand
            && Percentage >= 0.0 && Percentage <= 0.10, UNICHAR(128077),
        CheckExpand
            && Percentage > 0.10 && Percentage < 1.0,  UNICHAR(128681)
    )
Thanks,
Oded Dror

Hi @Anonymous ,

 

Is the 'GLedger'[DD] the lowest level of your matrix? is it the last level when you expand your matrix?

 

Can you share a sample file?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Yes, but it dosen't seems working correctly.

I do have multiple column in the header that drill down and the measure hiding some of the values based on that.

I realize that we can't do conditional formatting on sub total or total without including the detail rows.

I gave up (Iv'e tried all the column in the matrix one by one and it desen't produce the expected results - maybe Microsoft will address that issue - conditional formatting is different between table and matrix)

You don't have to spend time on this issue - you can close it - you see we can't even close an issue without mark it as solution 

Thank you 

Oded Dror

 

Hi @Anonymous ,

 

If you are abble to share a sample file I can take a look at it, because since the calculation is based on a measure the context can change the result.

 

If you have sensative data please share it in private message.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi there,

 

I realize the calculation % (Actual Vs Budget) is wrong thats cause the formating to be wrong i need to calculate %
based on condition of th ematrix then apply the conditional formating to it. Let me work on that and get back to you if I need more help.

 

Thanks,

Oded Dror

Anonymous
Not applicable

Hi there,

 

This one works and I did conditional formatting based on that

 

Formatting =
VAR Percentage = [Actual Vs Budget]
VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||
    Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))
RETURN
If(CheckExpand = TRUE(),
   Percentage,0)
Thanks
Oded Dror

Hi @Anonymous ,

 

Glad I could give some pointers to get the solution don't forget to mark your result as the answer for this post to help others.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.