Help! explain this measure and why it isn't working consistently!!
I have a report that someone else made and they are not seeing some data show in the visuals that they would expect to see. After investigating I found out that the measures are behaving strangely. Basically there are 3 measures. One determines if there are any items on a SalesOrder that have a synthesisDepartment of "LEU Main - Tubes" : HasLEUMainTubes = VAR CurrentSalesOrder = MAX('vwOperationsBacklog'[SalesOrder]) RETURN CALCULATE ( COUNTROWS ( 'vwOperationsBacklog' ), 'vwOperationsBacklog'[SalesOrder] = CurrentSalesOrder, 'vwOperationsBacklog'[SynthesisDepartment] = "LEU Main - Tubes" ) One determines if there are any items on a SalesOrder that have a synthesisDepartment of "LEU Platinum" : HasLEUPlatinum = VAR CurrentSalesOrder = MAX('vwOperationsBacklog'[SalesOrder]) RETURN CALCULATE ( COUNTROWS ( 'vwOperationsBacklog' ), 'vwOperationsBacklog'[SalesOrder] = CurrentSalesOrder, 'vwOperationsBacklog'[SynthesisDepartment] = "LEU Platinum" ) And the third measure determines if the SalesOrder has items from both departments: HasBothLEUMainTubesAndPlatinum =IF ( [HasLEUMainTubes] > 0 && [HasLEUPlatinum] > 0, 1, 0) The visual is a table that displays a list of SalesOrders that have both Main and Platinum items. However, the users only want to see $ values and specific products that are from Main and have applied a visual level filter of Synthesis Department = "LEU Main - Tubes". Here is the issue: In most cases, this works out fine. Example order 9558544 which has 8 items in each of the two departments shows up in my main visual with only $ associated to the Main department. BUT example order 21583399 does NOT show up in the table even though it has 2 Platinum items and 3 Main items. I created a quick look at these 2 orders and the results of the 3 measures below (separated by Synth Dept) As soon as I apply a visual level filter to that same table (setting Dept = Leu Main - Tubes), this is the result: WHY DOES ONE ORDER WORK DIFFERENTLY FROM THE OTHER????? I also know I could probably come up with a better way of doing this (I did not develop these measures, a user did) but I ultimately want to know why it functions like this.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=1
Better 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) eyJrIjoiZWYwNzZlNzctOTc5NC00ZWU1LWI2OWMtYTZjYTI0MjIzMjEzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9create measures with filters
Hello, I need to create measures using some filter to be able to make some card visuals. here is what I did: I have my date base with all years information, so for making the "sales per year" i made to measures: 2025 = SUM(...) and filtered in 2025 poryjected sales. 2024 = CALCULATE([Facturación],SAMEPERIODLASTYEAR('Calendar'[Date])) - I created a measure of get the sales of previous year. than I made a measure to get the difference between both 2024 adn 2025 sales. WHAT I SHOULD DO: I made to charts one "increasing of sales" detailed all the "clientes" that has a positive result and I was using the filter panel. and another of "lost sales" with all the negative results. I need to add some cards to summarize the totals. I have one showing the total of sales in 2025 I need a card showing the total of increaisng sales that is "2,066,314" and a card showing the total of lost sales that is "-919,865" thank you!
Dashboard 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/ .
