How to calculate Route based on repeating cycle
HI, I have this table A: Here you can see id has different route. So, when route is not repeating than good path and if route is going back to any previous route, then its bad path. Like image below How to achieve final visuals like image below? Thanks
Enquiry about year to year % measurement
I have a date dimension table and 2 tables Fact tables. The fact tables are cases and breaches. the relationship is one case to many breaches. One date can have many cases. I would like to calculate the YTD and Previous YTD count of cases and % change for year, quarter for last few years. How can I do that? someone has provided some dax. My current formula is 1. YTD Incidents = CALCULATE([Number of cases],DATESYTD('Cases'[Lodged Date].[Date])) 2. PYTD Serious Incidents = CALCULATE( [YTD Incidents], SAMEPERIODLASTYEAR(' Cases'[Lodged Date].[Date])) 3. YonY_Cases_%_Change = VAR CurrentRate = [YTD Incidents] VAR PriorRate = [PYTD Incidents] VAR PctChange = DIVIDE(CurrentRate - PriorRate, PriorRate, 0) VAR Arrow = IF(PctChange > 0, "▲ ", IF(PctChange < 0, "▼ ", "")) RETURN Arrow & FORMAT(PctChange, "0.0%") Someone has provided me the following DAX. how can i use that to generate YtoY % change, YTD vs PTD % change, quarter to quarter % change? thanks. VAR SelectedYear = SELECTEDVALUE('dimDate'[Year]) VAR MaxDataDate = [Latest Data Date] VAR StartDate = DATE(SelectedYear, 1, 1) VAR EndDate = IF( SelectedYear = YEAR(MaxDataDate), MaxDataDate, DATE(SelectedYear, 12, 31) ) And used StartDate and EndDate to filter Similarly, for previousYear: VAR SelectedYear = SELECTEDVALUE('dimDate'[Year]) VAR MaxDataDate = [Latest Data Date] VAR StartDate = DATE(SelectedYear - 1, 1, 1) VAR EndDate = IF( SelectedYear = YEAR(MaxDataDate), DATE( SelectedYear - 1, MONTH(MaxDataDate), DAY(MaxDataDate) ), DATE(SelectedYear - 1, 12, 31) )
ArcGIS for PBI-Tooltips
I'm using ArcGIS in a station pump read dashboard where I've overlayed rainfall data to find (if any) impacts of rainfall on our utilities system. Using the ArcGIS visual, which maps the stations throughout the city using symbols and colors to indicate which stations were impacted most during median high rain runtime and the relative rain response. The problem: the measures I'm using in the visual are represented with size and color on the map BUT when I put them in the Tooltips area, only one shows up, "Median high rain runtime per day". So I created a report page for tooltip and only 3 of 5 measures show up, one of them being "Median high rain runtime per day". This is what it looks like when I hover over a point on the map. My measures are: Median high rain runtime per day (show in report page tooltip) Median low rain runtime per day High rain period count (show in report page tooltip) Low rain period count (show in report page tooltip) Rain response % These measures work in other visuals and in a simple table. This is what the Report tooltip page looks like with all of them displayed: Here's the DAX for the above measures: Median Low-rain runtime per day = //This calculates the median combined runtime of all pumps at the station during low-rain periods. VAR StationPeriods = CALCULATETABLE( VALUES( 'Pump Readings'[Scatter Station Period ID] ), REMOVEFILTERS('Pump Readings'[Date]) ) VAR LowRainPeriods = FILTER( StationPeriods, VAR PeriodRainfall = CALCULATE( MAX( 'Pump Readings'[Rainfall During Reading Period] ) ) RETURN NOT ISBLANK(PeriodRainfall) && PeriodRainfall <= 0.25 ) RETURN MEDIANX( LowRainPeriods, CALCULATE( SUM( 'Pump Readings'[Runtime Hours Per Day] ) ) ) Median High-Rain runtime per Day = //This calculates the median combined runtime of all pumps at the station during high-rain periods. VAR StationPeriods = CALCULATETABLE(VALUES('Pump Readings'[Scatter Station Period ID]), REMOVEFILTERS('Pump Readings'[Date]) ) VAR HighRainPeriods = FILTER( StationPeriods, CALCULATE( MAX( 'Pump Readings'[Rainfall During Reading Period] ) ) >= 0.75 ) RETURN MEDIANX( HighRainPeriods, CALCULATE( SUM('Pump Readings'[Runtime Hours Per Day]) ) ) Median High-Rain runtime per Day = //This calculates the median combined runtime of all pumps at the station during high-rain periods. VAR StationPeriods = CALCULATETABLE(VALUES('Pump Readings'[Scatter Station Period ID]), REMOVEFILTERS('Pump Readings'[Date]) ) VAR HighRainPeriods = FILTER( StationPeriods, CALCULATE( MAX( 'Pump Readings'[Rainfall During Reading Period] ) ) >= 0.75 ) RETURN MEDIANX( HighRainPeriods, CALCULATE( SUM('Pump Readings'[Runtime Hours Per Day]) ) ) Low-Rain Period Count = //Create counts so one or two periods do not drive the result. VAR StationPeriods = CALCULATETABLE( VALUES('Pump Readings'[Scatter Station Period ID]), REMOVEFILTERS('Pump Readings'[Date]) ) RETURN COUNTROWS( FILTER( StationPeriods, CALCULATE( MAX( 'Pump Readings'[Rainfall During Reading Period] ) ) <= 0.25 ) ) Rain Response % = //Interpretation: 0%: no observable difference,25%: median runtime is 25% higher during high-rain periods, 100%: median runtime doubles, Negative value: runtime was lower during the high-rain periods VAR LowRainRuntime = [Median Low-rain runtime per day] VAR HighRainRuntime = [Median High-Rain runtime per Day] RETURN IF( ISBLANK(LowRainRuntime) || ISBLANK(HighRainRuntime) || LowRainRuntime = 0, BLANK(), DIVIDE( HighRainRuntime - LowRainRuntime, LowRainRuntime ) ) I've been troubleshooting this for about two days now and can't seem to find the solution. I've even rewritten the two measures that are not showing up. Any help is appreciated.
Azure Map - Issue with Conditional formatting
I am using an Azure Map visual which is connected to a table which has my Line ID in Location bucket and values in the Size bucket. There is a shapefile which is connected as a Reference Layer which has the same Line IDs. The lines are picking up the geometry from the shapefile correctly as well. I have defined rules using a DAX to conditionally format the reference layer lines. However, in the rules there is no reference to a colour blue for 'Level 2'. But the conditional formatting applied is working for some features in my file, whereas for other features it is defaulting back to blue colour. The DAX is correctly assigning the value of Level 2 to the line as well as I have attached in the below screenshots. Any idea on what is causing this/any ideas to troubleshoot this?
Power BI Tooltip – Dynamic sorting by value
Hi everyone, I would like to suggest an improvement to the standard tooltip in Power BI line charts. When a line chart contains multiple categories, such as different countries, the values in the standard tooltip are displayed according to the category or legend order rather than being dynamically sorted by their actual values. For example, if Italy has the highest value, Germany the second highest, and France the third highest, the chart clearly shows this ranking through the position of the lines. However, when hovering over a data point, the tooltip may still display the countries alphabetically: France – 80 €/MWh Germany – 100 €/MWh Italy – 120 €/MWh It would be much more intuitive if the standard tooltip could optionally sort the entries dynamically by the displayed value: Italy – 120 €/MWh Germany – 100 €/MWh France – 80 €/MWh There is a workaround by creating a custom report-page tooltip with a table or matrix and sorting it by the measure. However, I personally find the standard Power BI tooltip already very well designed and visually clean. Creating a custom tooltip just to change the sorting therefore feels unnecessarily cumbersome. It would be great if the existing standard tooltip could simply offer an option to sort the displayed categories dynamically by the selected measure, either ascending or descending. I think this would be a relatively small improvement but would make line charts with many categories much easier to interpret. Has anyone else experienced this or found another simple workaround? Thanks!
Change tooltip message based on measure being hovered over in a matrix
Hi, I am trying to get my tooltip value to display a custom value for each unique measure in the "Values" field in my Matrix visual. For example, I have the below fields in my visual. When I hover over a value in my matrix, I want a custom message to pop up based on the measure I have hovered over. Example: When I hover over a value associated with the "Calendar Month Settlement" measure below I want it to show "1" and if I hover over a value associated with the "WTI CMA" measure in the matrix I want it to show "2" etc. So I want my custom message to show up based on the measure I am hovering over. Is there a way I can write this in DAX?
