Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Before requesting help I've hacked through the community and can't find a solution. I work with commercial aviation data (passengers, seat capacity, flight frequencies, other).
Problem Statement
Calculation (Measure)
Attempts to Solve Problem
Formulas In Attached Screen Shots (all are measures)
Desired Outcome
I will create a visual. That visual could be a table or chart or other.
I want to:
Super big thank you in advance for your help and guidance.
DAXRichard
Houston, Texas
Solved! Go to Solution.
Hi @DAXRichArd
I am not sure I understand correctly what you are after. It sounds simple but because you provide a pretty long explanation I'm afraid I might be missing something. If it's what I understand, you can:
1. Create a calendar table to make things easier. You could use something like:
CalendarTable = ADDCOLUMNS ( FILTER ( CALENDARAUTO (); DAY ( [Date] ) = 1 ); "Year"; YEAR ( [Date] ); "Month"; MONTH ( [Date] ); "Month Name"; FORMAT ( [Date]; "mmm" ); "Day"; DAY ( [Date] ) )
or any other variant of your liking. Note I have filtered it down to include only the first day of the month since that's what your data seems to have. You could skip that and keep it all.
2. Create a relationship between CalendarTable[Date] and 'FT Passengers'[Date]
3. Create three simple measures:
Passengers = SUM('FT Passengers'[Pax])
MaxPassengers = MAX('FT Passengers'[Pax])
MinPassengers = MIN('FT Passengers'[Pax])
4. You can now place CalendarTable[Year] or CalendarTable[Month Name] in the slicers for date or add any other slicers from the 'FT Passengers' table.
Bear in mind that [MaxPassengers] and [MinPassengers] as defined above will provide the maximum (minimum) value found in the rows of the 'FT Passengers'[Pax] column under the context established by your slicers. That is a value that is in a single row. I am not sure that is what you need. I'd imagine you want some additional type of aggregation.
OK, then we need to update the [Passengers] measure we had before to ignore the year:
Passengers = CALCULATE ( SUM ( 'FT Passengers'[Pax] ); ALL ( CalendarTable[Year] ) )
With that I think you should be able to build your gauges. If you need more variations on what to consider from the slicers and what not, you'd need to update the measures again.
I am having hourly interval values summarized to MAX and MIN for a Month. But i need the MAX Date& Time and MIN Date and Time
how to fetch the MAX value along with time series data
in Power BI
Hi @DAXRichArd
It is better to split problems as several small problems, so that more sufficent solutions may be offerred.
Best Regards
Maggie
Hi @DAXRichArd
I am not sure I understand correctly what you are after. It sounds simple but because you provide a pretty long explanation I'm afraid I might be missing something. If it's what I understand, you can:
1. Create a calendar table to make things easier. You could use something like:
CalendarTable = ADDCOLUMNS ( FILTER ( CALENDARAUTO (); DAY ( [Date] ) = 1 ); "Year"; YEAR ( [Date] ); "Month"; MONTH ( [Date] ); "Month Name"; FORMAT ( [Date]; "mmm" ); "Day"; DAY ( [Date] ) )
or any other variant of your liking. Note I have filtered it down to include only the first day of the month since that's what your data seems to have. You could skip that and keep it all.
2. Create a relationship between CalendarTable[Date] and 'FT Passengers'[Date]
3. Create three simple measures:
Passengers = SUM('FT Passengers'[Pax])
MaxPassengers = MAX('FT Passengers'[Pax])
MinPassengers = MIN('FT Passengers'[Pax])
4. You can now place CalendarTable[Year] or CalendarTable[Month Name] in the slicers for date or add any other slicers from the 'FT Passengers' table.
Bear in mind that [MaxPassengers] and [MinPassengers] as defined above will provide the maximum (minimum) value found in the rows of the 'FT Passengers'[Pax] column under the context established by your slicers. That is a value that is in a single row. I am not sure that is what you need. I'd imagine you want some additional type of aggregation.
Hi AIB, Hi v-juanli-msft,
My desire is to use the minimum and maximun values in a guage visualization.
My organization often wants to know or understand what is our current state relative to our worst performance / best performance.
AIB, thanks to both of you for your replies. AIB, I hack at your recommendation and see how I can make it work.
Have a great year 2019!
DAXRichard
Let's try to clarify. Can you give me a concrete example of how things would work? For instance, I have this and that slicer with this month and this year selected (and maybe other things) and then I would expect my measures to yield this and that result because...
I am trying to understand exactly what you need so that we can finish this up.
Sorry for the inconvinience and again thank you for your follow through. I can't say thank you enough. The dashboard I want to create will look something like the attached image.
OK so let's say we have in the slicer the following:
Month: February
Year: 2015
Airport: HOU
Region: USA
What would be the result for each of the three measures you want? Explain conceptually how it would be calculated so that I can understand if I missed something earlier?
Hi AIB,
Month: February
Year: 2015
Airport: HOU
Region: USA
My gauges would show:
Thx
DAXRichard
Ok, so for the min and max we would be looking at the month February of all years in your data. Correct?
Good morning,
Sorry if this is a repeat reply (had internet connectivity problems from home this moring).
Your question:
Ok, so for the min and max we would be looking at the month February of all years in your data. Correct?
Response:
Yes, that is correct.
DAXRichard
OK, then we need to update the [Passengers] measure we had before to ignore the year:
Passengers = CALCULATE ( SUM ( 'FT Passengers'[Pax] ); ALL ( CalendarTable[Year] ) )
With that I think you should be able to build your gauges. If you need more variations on what to consider from the slicers and what not, you'd need to update the measures again.
Ahhh.... I'm my own worst enemy. I was explecting something complicated. I guess I was over engineering it. Big thanks AIB. I'll give it a try hopefully sometime today.
DAXRichard
Hi @DAXRichArd
Could you paste your table (or a fragment thereof showing all columns) in text format? I cannot read the screen capture you've posted.
If you prefer not to share personal details you can use filedropper.com for instance. It's completely anonymous, no sign-in
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |