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 everyone,
Maybe someone can help me with this problem.
I created a PieChart based on my dataset. The visual displays results of testcases with repect to a softwareversion. So some testcases will have multiple results depending on the amount of softwareversions. That is good and I need that for my whole report. So it makes no sense to create an extra column to find latest result.
Because for one visual I need to show only the latest result for one testcase I need a measure to filter out the latest.
Any ideas how that measure could look or if there is another way to do that on the visaual level only?
Thanks
Solved! Go to Solution.
Hi, @Anonymous
Latest_Date =
var MaxDate = CALCULATE(MAXX(Users,Users[Date]),
ALLEXCEPT(Users,Users[USER-ID]))
return MaxDate
Is this something that would fulfil your request?
@Anonymous That seems like a Lookup Min/Max situation:
Measure Lookup Max Simple =
VAR __Table = 'Table3'
VAR __Max = MAXX(__Table,[Column1])
RETURN
MAXX(FILTER(__Table,[Column1] = __Max),[Column])
Hi @Greg_Deckler ,
thanks for your idea, but it doesnt seem to work the way I need it to. Lets say I have two results of testcases with same ID but on different versions. I need the measure to filter out the one thats older. I tried your approach and its just showing the date of the result in the table instead of removing it. i had the ideo to assign 1s and 0s to it and than filter after 1s for the latest but dont really know how to do this with a measure.
@Anonymous Hard to be specific without sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
TC_ID | Version | Date | IsLatestResult |
1 | 23.3 | 10.10.2021 | 0 |
1 | 25.3 | 20.10.2021 | 1 |
2 | 23.3 | 10.10.2021 | 1 |
3 | 24.2 | 15.10.2021 | 1 |
Sorry that I cant show real sample data. But the idea goes like this. Where the IsLatestResult is the Measure. My PieChart should than only show the values that have 1s.
Hi, @Anonymous,
if you want to use it that way, then here is my solution in calculated column:
IsLatestResultCheck =
var latestdate = CALCULATE(MAXX('Table','Table'[Date]),ALLEXCEPT('Table','Table'[TC_ID]))
var check = if('Table'[Date]=latestdate,1,0)
return check
Tho, I would prepare my earlier solution, where you would instead of "Date" display just the "New Latest Date", that way you will see only the latest.
Like this:
I think this wont work, because I have a lot more versions and results and testcases in my dataset. And in my visual I only want to do this for selected versions nad if I would do a calculated column it would a lot of times filter out the results I want because its not the latest result in total but it would be latest on selection. Yout other solution would suite better if it wouldnt still display all results in my case.
@Anonymous
I see your point, however, without precise and specific conditions and requirements for the visualisations, I cannot help more.
Tho, you can also create a Calculated Table, with only the Latest Dates, that way you can display the request visualisation.
IsLatestResultCheck =
var latestdate = CALCULATE(MAXX('Table','Table'[Date]),ALLEXCEPT('Table','Table'[TC_ID]))
var MaxedTable = CALCULATETABLE('Table', FILTER('Table','Table'[Date]=latestdate))
return MaxedTable
No worries. Thanks for your help anyways, for now I will accept your solution as it seems to be really close to what I need. thank you.
Hi, @Anonymous,
it's possible to filter in visual using TOPN, see pictures:
Hi, @vojtechsima
This is not really what I am looking for. In your example the name or email is equivalent to my testcase. I need the last result for every name(testcase) and not the latest in total. So in you example if there would be a name more than once, than only show the one thats latest. But thanks for your effort.
Hi, @Anonymous
Latest_Date =
var MaxDate = CALCULATE(MAXX(Users,Users[Date]),
ALLEXCEPT(Users,Users[USER-ID]))
return MaxDate
Is this something that would fulfil your request?
Hey @vojtechsima ,
I have a Follow-Up question. is this also possible for only the selected values. I think this method refers to all values that are in Users. Can I also just refer to the values in table visual? So that the measure only takes these values into account instead of all?
Thanks
Hi, @Anonymous,
You can filter only Selected Values using ALLSELECTED function,
please refer here:
https://docs.microsoft.com/en-us/dax/allselected-function-dax
Thanks for the fast reply,
But if I do that it results in the same problem as state here.
https://community.powerbi.com/t5/Desktop/Measure-not-calculating-all-rows/m-p/2172462#M797802
And a lot of rows stay empty
This seems to be exactly what im looking for but if im using it, it doesnt remove the older values from the list. It just shows the latest date for both values. So still same amount of values...
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |