Conditional coloring by rank not applying to time-series chart
Hi everyone, my model looks like: I’m using the following measures to color visuals dynamically based on ranking: Rank (Current Axis) = VAR Result = IF ( ISINSCOPE ( fct_amortizedcosts[MeterSubCategory] ), RANKX ( ALLSELECTED ( fct_amortizedcosts[MeterSubCategory] ), CALCULATE ( [Total Amortized Cost] ), , DESC, DENSE ), IF ( ISINSCOPE ( fct_amortizedcosts[MeterCategory] ), RANKX ( ALLSELECTED ( fct_amortizedcosts[MeterCategory] ), CALCULATE ( [Total Amortized Cost] ), , DESC, DENSE ), BLANK () ) ) RETURN Result Color by Rank = VAR r = [Rank (Current Axis)] RETURN SWITCH ( TRUE(), ISBLANK ( r ) || r > 5, "#B3B3B3", r = 1, "#E81123", r = 2, "#107C10", r = 3, "#0078D4", r = 4, "#FFB900", r = 5, "#8E8CD8" ) This works perfectly in donut and bar charts, but when I try to apply the same color logic to a Line and clustered column chart (with Date on the X-axis), all columns turn gray: Has anyone managed to make field value–based coloring work on a time-series chart when the rank is calculated by category (MeterCategory) and not by the date axis? Example mode, use Problem to solve page: https://drive.google.com/file/d/14lpEWK-MQ6JMU5_15WVh8Gv22AiGvdpR/view?usp=drive_link I think that MeterCategory column does not exist in specific visual and this is why visual does not read it as ISINSCOPE function. I tried with HasONeValue but the same i think it is in visual level, not page level filter context...how to pass this ? Thanks for any suggestions!
Conditional format latest value per user
I'm working on a report which pulls together several sources of training data. One of the widgets we need is for new employees working through the training program and capturing their training events by week. The ask from stakeholders is to color code a given user's lastest training week which I felt I could accomplish from conditional formatting. I have the following formulas: 1. Week of Training Week of Training = var saturdaybefore = CALCULATE (MIN('Employee'[Proficiency Training Start Date]) - WEEKDAY(MIN('Employee'[Proficiency Training Start Date]), 2))-1 RETURN IF(YEAR(saturdaybefore) < YEAR(MIN('Employee'[Proficiency Training Start Date])), BLANK(), ROUNDUP(DIVIDE(DATEDIFF(saturdaybefore, 'Data'[Date of Training], Day),7), 0)) 2. I then created a measure to assess the max week of each user: Measure = var maxweek = CALCULATE( MAXX('Data',[Week of Training] ), ALLEXCEPT(Data', 'Data'[USERNAME]) ) RETURN IF(ISBLANK(maxweek), 0, IF(Max('Data'[Week of Training]) =maxweek, 2, 1) ) I want to color a given user's maximum week orange. I thought I could accompish doing this with my measure above and setting 2 to be orange (the field format is number), but it's only formatting the absolute latest training date (e.g., green box value) and not everyone's latest training week date (e.g. red box values). I realize I am currently using gradient conditional formatting but it shouldn't affect that the latest training week for a user isn't shading properly. Here's some truncated sample data to show you my measure is working properly: USERNAME Date of Training SYSID Proficiency Start Date Week of Training Measure user1 11/6/2024 00:00 8609789 0 user2 1/21/2025 00:00 9043094 10/7/2024 00:00 16 2 user2 12/15/2024 00:00 8873091 10/7/2024 00:00 11 1 user2 12/11/2024 00:00 8850769 10/7/2024 00:00 10 1 user2 12/11/2024 00:00 8850741 10/7/2024 00:00 10 1 user2 12/2/2024 00:00 8786133 10/7/2024 00:00 9 1 user2 12/1/2024 00:00 8777767 10/7/2024 00:00 9 1 user2 12/1/2024 00:00 8777763 10/7/2024 00:00 9 1 user2 11/30/2024 00:00 8775141 10/7/2024 00:00 8 1 user2 11/30/2024 00:00 8775133 10/7/2024 00:00 8 1 user2 11/27/2024 00:00 8760819 10/7/2024 00:00 8 1 user2 11/17/2024 00:00 8684304 10/7/2024 00:00 7 1 user2 11/13/2024 00:00 8659422 10/7/2024 00:00 6 1 user2 11/4/2024 00:00 8592577 10/7/2024 00:00 5 1 user2 11/3/2024 00:00 8584769 10/7/2024 00:00 5 1 user2 10/30/2024 00:00 8565134 10/7/2024 00:00 4 1 user2 10/30/2024 00:00 8565131 10/7/2024 00:00 4 1 user2 10/30/2024 00:00 8565127 10/7/2024 00:00 4 1 user2 10/24/2024 00:00 8526851 10/7/2024 00:00 3 1 user2 10/22/2024 00:00 8505041 10/7/2024 00:00 3 1 user2 10/22/2024 00:00 8505026 10/7/2024 00:00 3 1 user2 10/16/2024 00:00 8467188 10/7/2024 00:00 2 1 user2 10/16/2024 00:00 8467188 10/7/2024 00:00 2 1 user2 10/16/2024 00:00 8467188 10/7/2024 00:00 2 1 user2 10/11/2024 00:00 8439690 10/7/2024 00:00 1 1 user2 10/11/2024 00:00 8439690 10/7/2024 00:00 1 1 user3 12/14/2024 00:00 8870221 10/7/2024 00:00 10 2 user3 12/8/2024 00:00 8827785 10/7/2024 00:00 10 2 user3 12/5/2024 00:00 8807877 10/7/2024 00:00 9 1 user3 12/4/2024 00:00 8807664 10/7/2024 00:00 9 1 user3 12/4/2024 00:00 8806990 10/7/2024 00:00 9 1 user3 11/26/2024 00:00 8744739 10/7/2024 00:00 8 1 user3 11/25/2024 00:00 8737160 10/7/2024 00:00 8 1 user3 11/24/2024 00:00 8732020 10/7/2024 00:00 8 1 user3 11/23/2024 00:00 8731822 10/7/2024 00:00 7 1 user3 11/20/2024 00:00 8710436 10/7/2024 00:00 7 1 user3 11/10/2024 00:00 8633342 10/7/2024 00:00 6 1 user3 11/10/2024 00:00 8633326 10/7/2024 00:00 6 1 user3 11/9/2024 00:00 8630349 10/7/2024 00:00 5 1 user3 11/6/2024 00:00 8603308 10/7/2024 00:00 5 1 user3 11/1/2024 00:00 8578294 10/7/2024 00:00 4 1 user3 10/23/2024 00:00 8517792 10/7/2024 00:00 3 1 user3 10/22/2024 00:00 8509909 10/7/2024 00:00 3 1 user3 10/19/2024 00:00 8488369 10/7/2024 00:00 2 1 user3 10/19/2024 00:00 8488358 10/7/2024 00:00 2 1 user3 10/9/2024 00:00 8425942 10/7/2024 00:00 1 1
Conditional formatting highlighting selected value in slicer
I would like to use conditional formatting to highlight a specific bar in my bar chart, based on the value I have selected in my slicer. All over the internet, I find the same DAX formula which is the following: HighlightCompany = IF( SELECTEDVALUE(Company[CompanyName]) = MAX(Company[CompanyName]), 1, // This means the company is selected, and it will be highlighted 0 // This means the company is not selected ) Although, when I do this DAX formula, all bars are highlighted. I thought it might've been because my slicer is not filtering the bar chart, although when I choose to filter the bar chart, it only shows the selected company in the bar chart, without all the other companies. Here are some pictures: In this one, the selected company (on the left) only returns that company in the bar chart (on the right). In this one, the selected company (on the left) returns all companies on the right, without highlighting the selected company. When I use a dax formula, either one of both pops up, and not a combination of both. Does anyone have the same issue or have a solution for this problem? Thank you in advance!Individual Row Colors in Matrix
Hello! I am attempting to color individual rows of a matrix. I understand this is not currently a feature in Power BI, but using this YouTube video, I was able to find a work around. I used the below measure, which I used as Conditional Formatting, to color the rows based on the HEX color listed in each row of the measure. However, this seems to exclude cells with a zero/blank value. How can I modify the below measure to ensure all cells for each row have the same formatting? For instance, "In Transit Receipts" shows many white cells because they have no value. I would like these cells to be the same light blue color as the cells with a value. Metric Colors = SWITCH( TRUE(), MAX('APO Monthly'[MetricNew])="Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="Sales Order","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Sales Orders","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="DI Forecast","#e6e6e6", MAX('APO Monthly'[MetricNew])="Stock Transfer (Out)","White", MAX('APO Monthly'[MetricNew])="SAP Suggested Buy","#e6e6e6", max('APO Monthly'[MetricNew])="In Transit Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Confirmed Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Additional Confirmed Receipts (Inc STO In)","#d9e1f2", MAX('APO Monthly'[MetricNew])="Total Demand","#09124f", MAX('APO Monthly'[MetricNew])="Total Receipts","#09124f", max('APO Monthly'[MetricNew])="Stock On Hand (Projected)","#055201", "White" ) Thank you!
Conditional Format Blank Values for Specified Column
I am trying to create a DAX formula to conditionally format the blank values for a specified column. In the below visual, I want all the blank values under "Renovo", "6/24", and "%" to show as the trianglelow icon. Below is my formula: Color = IF( SELECTEDVALUE('Transactions Export'[Previous Month Filter])="Previous Month" && SELECTEDVALUE('Transactions Export'[Lender v3])="RENOVO" && ISBLANK([Transactions %]),"TriangleLow") No icons appear with my formula. How can I acheive the desired result?Conditional Formatting Only Active for Specific Column Value
I've created a row based conditional formatting DAX formula, but I don't want the conditional formatting to be active for the "Lender" column where Lender="COMP" Is there anyway that I can get my formula to ignore the values that fall under "Comp" so that they are not highlighted in any colors? In other words, I only want the "RF" values to be highlighted in colors. Below is more formula: Color = IF( NOT(ISBLANK([Transactions])), RANKX(CALCULATETABLE( ALLSELECTED('Transactions Export'[Lender])), [Transactions],,,Dense ) )DAX Measure and Background Colour Not Working
I have written a DAX and upon that I want to do color Coding based on certain conditions, DAX Code Reporting months = var MAXD=MAX('Dim_Date'[MMDDYYYY]) var Last13M = DATESINPERIOD('ABCD'[DateOfSample],MAXD,-12,MONTH) RETURN Calculate(Sum('ABCD'[Value]),REMOVEFILTERS('Dim_Date'),KEEPFILTERS(Last13M),USERELATIONSHIP('Dim_Date'[MMDDYYYY],'ABCD'[DateOfSample])) I Have three columns now, Value, EFG, ZPR Based on the condition on the above three columns, I need to give a background color. It could be like DAX M = SWITCH(TRUE(), Value > 1, "Some color" Value = -1 , "Some color" Value > EFG, " Some Color", Value < ZPR, "Some Color" "Some color") But when I do it in the conditional formatting, it doesn't work and highlights only the selected month-year combination and nothing apart from it. You can also see we are using Value column in the measure. Any heads up?Conditional Formatting with Calculation Groups - Issues, please help me
Hi community, I developed a model with some conditional formatting, and I'm quite happy with it. Since the number of measures is quickly growing, I've started to implement Calculation Groups, to make it more efficient, and the model actually works fine with calculation groups implemented. Despite that, I found some issues with a simple conditional formatting configuration. Download - Sample Model (300 kb): https://www.dropbox.com/scl/fi/4sju5841r4m3afq7tewdo/Help_CalcGroups.zip?rlkey=v65nx5eshluxkz310ta7jacxe&dl=0 My basic conditional formatted visual without calculation groups looks as follows, and works just fine. The formatting condition is pretty simple and based on one field. The background of the rows of the table 'PL_Structure' is light blue where the field 'PL_Structure' [PL_Detail] has value 0 (running total rows), while are colored in light grey where the field value is 2 (subtotal row). The other - common rows - stay white (default color). The structure of the table can be found in the attachment together with the model (DataSource.xlsx), and includes an explanation of key fields as well. My formatting conditions The "PL SubTot Color" measure simply reads the SELECTEDVALUE of the above mentioned [PL_Detail] field): Now, with calculation groups implemented, I'm experimenting a strange behaviour, the numbers are just ok, but the table looks as follows: Maybe I did not fully understand the logics behind calculation groups, but I find them extremely useful for maintenance purposes. The attached model includes a side by side (with / without calculation groups). I do appreciate any suggestion or fix. Thank you. Marco
