Line Segment and Legend Color Based on Measures
Conditional formatting in Power BI just got a solid upgrade. In the July 2026 update, conditional formatting now works on Line Charts and visuals with Legends. This was one of the most requested features. It lets you apply data-driven colors across multiple visual types and keep colors consistent throughout your reports. Up until recently, when I used to interview Power BI professionals with a lot of experience in visualization, I had a good way to test them. I'd ask one of these questions: Which visuals don't support conditional formatting? I made some changes to my visual and now the conditional formatting option isn't showing. Why? How do you apply conditional formatting on Line and Pie visuals? It was an easy way to catch gaps. Conditional formatting wasn't supported on legends. For Pie visuals, the workaround was to apply it on a Bar chart first and then switch to Pie. For Line visuals, we used to get extra color markers the same way. Power BI has been improving its visualization experience steadily over the last two years, with big updates coming regularly. This long-awaited feature finally made it into the July 2026 update. Conditional formatting is now supported on Line visuals and Legends. Why it was needed on Line visuals Just like any other visual, you sometimes need line segments to change color based on a condition. This helps explain things like margins going down or current year vs previous year comparisons. Why it was needed on Legends Say your company always uses one color and your competitor uses another. On a report showing market share, you'd always want your company represented by the same color. That was possible for axis values but not for legends. This update fixes that. You can now apply conditional formatting on Pie, Stacked, and any other visual that uses a legend. Please find the file where I have used different measures to do conditional formatting on Line and Legends Measure used Brand Color = SWITCH ( TRUE (), Max('Item'[Brand])= "Brand 1", "Yellow", Max('Item'[Brand])= "Brand 2", "Green", Max('Item'[Brand])= "Brand 3", "Blue", Max('Item'[Brand])= "Brand 4", "Red", Max('Item'[Brand])= "Brand 5", "Orange", Max('Item'[Brand])= "Brand 6", "Purple", Max('Item'[Brand])= "Brand 7", "Pink", Max('Item'[Brand])= "Brand 8", "Cyan", Max('Item'[Brand])= "Brand 9", "Lime", Max('Item'[Brand])= "Brand 10", "Brown", Max('Item'[Brand])= "Brand 11", "Gray", Max('Item'[Brand])= "Brand 12", "Teal", Max('Item'[Brand])= "Brand 13", "Magenta", "Other" ) Category Measure Category Color = SWITCH ( TRUE (), Max('Item'[Category] )= "Category 1", "Red", Max('Item'[Category] )= "Category 2", "Green", Max('Item'[Category] )= "Category 3", "Blue", Max('Item'[Category] )= "Category 4", "Yellow", Max('Item'[Category] )= "Category 5", "Orange", "Black" ) Color Year Max Year = max('Date'[Year]) You can also check the video on the same - https://www.youtube.com/watch?v=SDX1gUpcJaw&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1Better Rolling Average
Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, Better Sales from New Customers, and Year to Date Total and Year Over Year Change, this one tackles Rolling Average. Power BI's Rolling Average quick measure returns something like this: Value rolling average = IF( ISFILTERED('Dates'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('Dates'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'Dates'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Dates'), 'Dates'[Date].[Year], 'Dates'[Date].[QuarterNo], 'Dates'[Date].[Quarter], 'Dates'[Date].[MonthNo], 'Dates'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('Table'[Value]), ALL('Dates'[Date].[Day])) ) ) Perhaps a better way: Better Rolling Average = VAR __EndDate = MAX('Table'[Date]) VAR __3MonthsAgo = EOMONTH(__EndDate, -3) VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1) VAR __Table = SUMMARIZE( FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate), 'Table'[Month], "__Value",SUM('Table'[Value]) ) RETURN AVERAGEX(__Table,[__Value]) And the video: eyJrIjoiZjc5MDlhYjktOWYzZi00YzM3LWFlYWEtYWMyMGQyNzM4NGYwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Patient Cohort (AND Slicer)
In the healthcare field, it is often desireable to identify a cohort of patients with similar, multiple diagnoses. This quick measure returns a comma-delimited list of patients that have all been identified with the same diagnoses. The tricky part here is that this allows the user to select from a slicer the diagnoses for which the user is interested in obtaining a cohort. Identified patients have had diagnoses that meet all of the selected criteria. In other words, all patients have had diagnoses for all of the selected diagnoses in the slicer. Essentially creates an AND for the slicer as opposed to the normal OR. Cohort = VAR tmpTable1 = GENERATE(VALUES(Diagnosis[Patient]), EXCEPT( VALUES(Diagnosis[Diagnosis]), CALCULATETABLE(VALUES(Diagnosis[Diagnosis])))) VAR tmpTable2 = SUMMARIZE(tmpTable1,Diagnosis[Patient]) VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2) RETURN CONCATENATEX(tmpTable3,[Patient],",") This quick measure would take two inputs, the column for the ID to return (Patient) and the column for the slicer selection (Diagnosis) Also included is the trivial variation, Count of Cohort: Count of Cohort = VAR tmpTable1 = GENERATE(VALUES(Diagnosis[Patient]), EXCEPT( VALUES(Diagnosis[Diagnosis]), CALCULATETABLE(VALUES(Diagnosis[Diagnosis])))) VAR tmpTable2 = SUMMARIZE(tmpTable1,Diagnosis[Patient]) VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2) RETURN COUNTROWS(tmpTable3) eyJrIjoiZWYwNzZlNzctOTc5NC00ZWU1LWI2OWMtYTZjYTI0MjIzMjEzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Dashboard Template
A Power BI template featuring a heatmap as a calendar-formatted matrix and buttons that reference bookmarks for day, week, month, quarter, and year to adjust the date timeline selection and the X-axis date hierarchy on trend charts. It includes date filtering options based on timeframes and ageing. This template was developed for on-premises Power BI Report Server but also works in the Service. The test data was created by using https://mockaroo.com/ .Market Basket Analysis - Bulk Search
I was assigned to analyze a sales report and conduct a basket analysis for inventory management to discontinue some products. This experience transformed how I think about data and my approach to problem-solving. Learn Market Basket Analysis, Association Algorithm Techniques, and Association Rules Viewer Useful Resource The Knowledge Bank by obviEnce , SQLBI, and ViSIT Selection Customers = CALCULATE ([Unique Customers], TREATAS(VALUES('SelectedItem'[ITEM]), 'Sales Line'[Item])) Basket Analysis = VAR v1 = SELECTEDVALUE('SelectedItem'[ITEM]) VAR v2 = SELECTEDVALUE('GeneralBasket'[ITEM]) VAR V3 = SELECTEDVALUE('AnalysisType'[Type]) VAR v4 = SUMMARIZE(FILTER('Sales Line', RELATED('Sales Line -Duplicate'[Item]) = v1),'Sales Line'[Order #]) VAR v5 = SUMMARIZE(v4, 'Sales Line'[Order #], "Basket" , CONCATENATEX(RELATEDTABLE('Sales Line'), 'Sales Line'[Item], "|") ) VAR v6 = SUMX(v4,1) VAR v7 = SUMX( v5, IF(PATHCONTAINS([Basket], v2),1, BLANK())) RETURN SWITCH(TRUE(), v1 <> v2 && V3 = "% Second Product is Purchased with First", DIVIDE( v7,v6, BLANK() ), v1 <> v2 && V3 = "Count of Shared Baskets", v7, v1 <> v2 && V3 = "count of first Product Purchase", v6, BLANK () ) eyJrIjoiMDgyNDlkNjAtZGRjZS00NjE0LWEwZDktMTRkMWQwYWFjNTBiIiwidCI6IjNlMjFhMTFlLTc3MDctNDdmOC1iMzRhLTc5YTQ2YTQ0ZTk5MyIsImMiOjF9Increased, Decreased, No Change
Came out of this post here: Re: Looking for help with measures please - Microsoft Fabric Community Basically, given a set of date-series data, how many customers have increased their score, descreased their score, or remained unchanged from their first data point to their last data point. Also calculates the percentage that have increased, decreased, or remained the same as well as the average number of data points for customers that have increased, decreased, or remained the same. The measures below are for increases, the other measures are nearly identical and are in the attached PBIX file. Updated to include a variation that excludes customers with only 1 appointment. Total Icreased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __Result = COUNTROWS( FILTER( __Table, [__Diff] > 0 ) ) RETURN __Result Total Percent Increased Measure = DIVIDE( [Total Icreased Measure], COUNTROWS(DISTINCT('Table'[Customer ID])), 0) Average Appointments Increased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __IncreasedCustomers = DISTINCT( SELECTCOLUMNS( FILTER( __Table, [__Diff] > 0 ), "__ID", [Customer ID] ) ) VAR __Result = AVERAGEX( SUMMARIZE( FILTER( 'Table', [Customer ID] IN __IncreasedCustomers ), [Customer ID], "__Count", COUNTROWS('Table') ), [__Count] ) RETURN __Result eyJrIjoiZmYxMTUxYjUtMTg2Ni00OTcyLWFkMzQtMmY0ZDBhMmYzMTFkIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9Power BI Interview Questions -101 Interview Questions| Power BI 101 Concepts
In today’s data-driven world, the demand for effective data analysis and visualization tools has skyrocketed, and Power BI has emerged as a leading solution in the realm of business intelligence. As organizations seek to make data-informed decisions, Power BI’s versatility and user-friendly interface have made it a top choice for professionals across various industries. For individuals preparing for a Power BI interview, demonstrating a strong grasp of the tool’s functionalities and data manipulation capabilities is essential to stand out from the competition. In this comprehensive blog post, we will delve into the most commonly asked Power BI interview questions and provide insightful answers to help you excel in your interview. As Power BI interviewers, we value both functional knowledge and strong technical expertise. We aim to ensure that candidates not only understand the concepts but have practical experience working with Power BI. To assess your hands-on experience, we may tweak questions to check the application of concepts. For instance, we might present a scenario to gauge your practical skills instead of asking a basic question about interactions. For example, we could ask: “In a Power BI report, you have a page that contains two slicers say- Slicer 1 and Slicer 2. Additionally, there are two Line visuals assuming Line Visual 1 and Line Visual 2 are on the same page. How would you make sure that Slicer 1 only filter Line Visual 1, and Slicer 2 only filter Line Visual 2?” By presenting such real-world scenarios, we aim to assess your ability to apply Power BI concepts in practical situations. Your response to this question will demonstrate your technical understanding of setting up interactions and will reveal your familiarity with the Power BI interface and functionalities. Throughout the interview, we may present similar scenarios and technical challenges to further evaluate your expertise. This approach enables us to determine your level of proficiency in Power BI. It ensures that we select candidates who not only possess theoretical knowledge but can also effectively translate that knowledge into real-life projects and reporting tasks. Organizations encourage you to come prepared with hands-on experience and be ready to showcase your practical skills. Demonstrating your ability to work with Power BI confidently will significantly enhance your chances of excelling in the interview process and becoming a valuable addition to the team. You can find the first 40 questions here https://medium.com/microsoft-power-bi/power-bi-interview-questions-part-1-4982de3be327 You can find the Next 61 questions here Power BI Interview Questions- How to Crack Power BI Interview- 101 Questions | Medium The file for the last 61 questions is attached at the end of this article.
Dynamic Hierarchical Row Level Security
I've recently been at a couple of Power BI Conferences where one of the topics which generated some confusion to Citizen Power BI Developers was Dynamic Row Level Security and the DAX related to it. In this article, I will explain it in a very easy and understandable way, and go through every used function step by step. As it's not possible to view the model and formulas online, neither publish to web with active RLS, I suggest the following: - Download the PBIX. This will enable you to see the underlying model, formulas, and view as another person. - Make sure to read the blog post here. It will show you the DAX formulas step by step. eyJrIjoiYzlkODZlNDQtYWFjMy00MWY2LTg0MWUtZDk4Y2IzZjk1MzljIiwidCI6IjMzY2EzNmVmLTU5NWItNDU3Ni1iMjVkLWRiNDk4ZmM1OWVhMiIsImMiOjl9Learn Power BI: Tutorial for Beginners -Full Course
Learn Power BI Tutorial/training for Beginners, full/complete course- 11 hours. DAX, Power Query, and Power Platform overview, Load Data, Calculated Column, Measure, Visuals, Field Parameters, Bookmarks, Offset, Window, Index Functions, Time Intelligence, Interactions, Drill Through, Tooltip, Publish file, Pivot data, Unpivot data, Transpose data, Delete Blank and Duplicates. Full Video Link: https://www.youtube.com/watch?v=cN8AO3_vmlY Data: Covid 19: https://covid19.who.int/WHO-COVID-19-global-data.csv Get The data and Pbix files https://github.com/amitchandakpbi/powerbi/tree/main/Data%20for%20Learn%20Power%20BI%20Full%20Video Other File at GitHub https://github.com/amitchandakpbi/powerbi More Videos that you should watch How to create a login at app.powerbi.com: https://youtu.be/VW130u7u-G0 Incremental Refresh Pro; Premium Deployment pipeline - https://youtu.be/nIxTRdeCYSE On-Premise Gateway and configure it on service: https://youtu.be/lObMRofpbQ8 Dataflows and Dataset Design Pattern: https://youtu.be/zwhJ1hWPcrA Power BI- How to create Datamarts: https://youtu.be/8tskWsJTEpg Mastering Power BI: https://www.youtube.com/watch?v=wvsAzTqSDVg&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb Expertise Power BI: https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L Following are the 100+ Topics 1. Introduction 2. Basic Concepts 3. Download and Install Power BI 4. Quick Look at Power BI Desktop Dec 2022 5. Create Power Bi Login 6. Login/Sign in to Power B 7. Power BI EcoSystem 8. Power Bi Licenses 9. Power Bi Desktop Overview 10. Power BI Options & Settings 11. Covid Data Analysis 12. Download the data 13. Overview of Data 14. Load Data into Power BI 15. Column Tools 16. Data Model Property Pane 17. Check No Join issue 18. Single Vs Bi-Directional Join 19. Update Power BI Theme 20. Create Calculated Columns and Measures 21. Calculate with and Without filter 22. Expression Functions SUMX,MINX, MAXX 23. isfiltered, hasonevalue 24. Table Visual 25. Gradient Conditional Formatting 26. Rule based Conditional Formatting 27. Data Bars 28. URL Icon 29. Matrix Visual 30. Switch Values to Row 31. Stepped Layout off 32. Desc Sort on Matrix Column 33. Bar Visual 34. Concatenate Label off 35. Measure Based Conditional Formatting 36. Pie/Donut Visual 37. Line Visual 38. Area Visual 39. Tree Map Visual 40. Stacked Visual 41. Combo Visual 42. Small Multiples 43. Card Visual 44. Multi Row Card 45. Scatter Visual 46. Scatter Visual with quadrants conditional formatting 47. Map, Shape Map, Filled Map, Security Settings 48. Decomposition Tree 49. Q&A Visual 50. DAX Earlier 51. Date Table 52. Date Table FY Columns 53. DAX Search, Find 54. DAX containsstring, containsstringexact 55. DAX Left, Right, Mid 56. calculatetable 57. Summarize 58. Summarize Vs Groupby 59. Summarize in Measures 60. SummarizeColumns, Except, Distinct 61. Generateseries 62. Generate, Cross join 63. all, allselected, removefilters 64. allexcept 65. rankx, topn 66. dynamic topn with numeric parameters 67. Firstnonblankvalue, Lastnonblankvalue 68. Distinct count using values/summarize 69. Power BI Time Intelligence Setup 70. totalmtd, datesmtd, datesqtd, datesytd 71. Last datesmtd, Last datesqtd, Last datesytd 72. Trailing Day, Week, Month, Qtr, Year, Year Week 73. Previousmonth, previousquarter, previousyear 74. Week on Week, WTD 75. Half(custom period) Till Date, HoH,Period on Period 76. DAX offset 77. DAX isinscope 78. DAX window 79. DAX Index 80. Power BI Visual Interaction 81. Power BI Bookmarks 82. Field Parameter, Measure Slicer, Axis Slicer 83. Drill Through 84. Tooltip 85. Introduction to Power Query 86. Use First Row As a header, Remove Blank and Duplicates 87. Power Query UnPivot Data 88. Power Query Pivot Data 89. Power Query Transpose Data 90. Power Query Fill Up, Fill Down 91. Power Query Replace Values 92. Power Query Text Operations 93. Power Query Append Tables 94. Power Query Merge Tables 95. Power Query Custom Column 96. Power Query Extract Data 97. Column Quality, Profile and Distribution 98. Clean Power BI File 99. Publish Power BI File, Analyze on Power Bi Service 100. Create Report in Power BI Service 101. Power Bi Service Dashboard 102. Power Bi Service App 103. Power BI Dataflow 104. Power BI Live Connection 105. Power Bi Tenant SettingLookup Min/Max
This one or some variation of it comes up constantly in the forums so I'm surprised that there is no pattern in the Quick Measure Gallery for dealing with it. The problem generally goes along the lines of this: "I have this table of data with these items in it and values for various blah. I need to create a measure that returns which item has the lowest/highest sum/average/blah." That's not a direct quote, I'm paraphrasing... Anyway, the pattern looks like this: Lookup Min = VAR __Table = SUMMARIZE( 'Table', [Item], "__Value",SUM('Table'[Value]) ) VAR __Min = MINX(__Table,[__Value]) RETURN MINX(FILTER(__Table,[__Value] = __Min),[Item]) Lookup Max = VAR __Table = SUMMARIZE( 'Table', [Item], "__Value",SUM('Table'[Value]) ) VAR __Max = MAXX(__Table,[__Value]) RETURN MAXX(FILTER(__Table,[__Value] = __Max),[Item]) eyJrIjoiNjA5NzBlMWMtZDY0ZS00N2I1LWFmM2EtNmJlOTJmMjZiYzJhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
