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.
Hi,
I have the following table. There are 8 machine ID's. What I need is a table which shows the color of the maximum last status date/time. So for machine 1 this is the color red, for machine 2 color red, machine 3 color orange...etc. I tried selected value and lookup value together with maximum last status date / time, but don't get the right solution. Please advise! Thanks in advance.
Solved! Go to Solution.
Hi @Hyperchef1969 ,
Please use below measure:
Measure = var a = CALCULATE(MAX('Table'[LastStatus]),ALLEXCEPT('Table','Table'[Machine ID])) Return IF(MAX('Table'[LastStatus])=a,1,0)
Then filter the measure = 1 :
Without showing you the code, proceed one step at a time (in a calc column)
- first FILTER out your rows (using the machineid) -> now you have your 4 rows
- use TOPN to retrieve the top 1 row based on last status -> now you have 1 row
- use SELECTCOLUMNS to extract the column you need -> now you have 1 row and 1 column
so in pseudo code is
status=SELECTCOLUMNS(TOPN(1,FILTER(yourfilter on machine id),Laststatus),"any name",yourtable[machine_status])
to be very honest, this is the solution I use in similar cases. I understand is convoluted and maybe there are better solution, so if anyone has better ideas, I am looking forward to.
One important caveat: TOPN returns multiple rows if there is a tie (i.e. if there are more than one row with the same "top" last date). So it would be better to protect yourself from future occurences of this situation by either including in another TOPN or using SUMMARIZE or other functions that returns just one row.
Hi,
Filtered on machine ID so that I have 4 rows. After that I can't implement the TopN into the visual (because it is a date?). Formula does not work. Can you be more specific to the solution? Thanks in advance!!
Add a calculated column in your table that puts a "1" for the max date rows (i.e. the ones you want to show).
Then add your data in a talbe and filter the table where your calculated column is 1
Hi,
Tried following column based calculation (working on direct query mode)
Then I get the message 'Function 'MAX' is not allowed as part of a calculated column DAX expressions on Direct Query models. Is there an alternative?
Hi @Hyperchef1969 ,
Please use below measure:
Measure = var a = CALCULATE(MAX('Table'[LastStatus]),ALLEXCEPT('Table','Table'[Machine ID])) Return IF(MAX('Table'[LastStatus])=a,1,0)
Then filter the measure = 1 :
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |