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 all,
I have a table with multiples entries the same day (the only difference is the time HH:mm/ss) and i need to graph the last value from each day given a period of time
I have this:
And i need to make a visual that shows the last value (based on the champ date/time ) of each row given a period of time through a filter. Something like this:
Filter Date from 01/08/2018 to 05/08/2018
table : Indicateurs
I am new with POwer BI, so any isdeas i really apprrecite
I've been trying with a Messure that identify the last value for each row but this is not working
Measure =
VAR LatestDate =
CALCULATE ( MAX ( 'Indicateurs'[Date]); ALL ( 'Indicateurs' ) )
RETURN
IF ( MIN ( 'Indicateurs'[Date]) = LatestDate;1,0)
Thanks in advance!!!!!
Solved! Go to Solution.
Hi, @rostromarre is this based on a sql query?
If it is you could add a new column to the query to give you the last row for an Id Contrentrateur on any particular day.
If it is SQL you could add in the edit quereis and edit source;
ROWNUMBER() OVER (PARTITION BY [Id Concentrateur],CONVERT(DATE,[Date]) ORDER BY [Date] DESC) AS LatestRow
This be 1 for all the rows you need so you could filter on this field being 1.
Is your Date a real Date/Time column? If so, something like this should work. I expect your issue was how you were using ALL:
Measure = VAR __id = MAX([Id Concentrateur]) VAR LatestDate = MAXX(FILTER(ALL('Indicateurs'),[Id Concentrateur]=__id), 'Indicateurs'[Date]) RETURN IF ( MIN ( 'Indicateurs'[Date]) = LatestDate;1,0)
thanks Greg.
Your idea is not working (I still don"t know how to fix it) but I think the mistake is with the Var __id, this varibale is picking up only the MAX value of th whole column Id Concentrateur, and actually it should take all the "id Concentratureur" I have within the period of time chosen (certainly more than one).
I trying to work from your idea, any help i really ppreciate!! thanks in advance
Hi, @rostromarre is this based on a sql query?
If it is you could add a new column to the query to give you the last row for an Id Contrentrateur on any particular day.
If it is SQL you could add in the edit quereis and edit source;
ROWNUMBER() OVER (PARTITION BY [Id Concentrateur],CONVERT(DATE,[Date]) ORDER BY [Date] DESC) AS LatestRow
This be 1 for all the rows you need so you could filter on this field being 1.
thanks @gooranga1
I did what you proposed and edited the source. I added the column in the extract query and after I manipulated the chart using the "latestROw" as a filter.
I really wanted to find a solution direclty in Power BI 😞 , but this is working quite good.. thanks a lot
Or you could try RankX?
I create a DateOnly column based on your screenshot like;
DateOnly = DATE(YEAR(Table12[Date]),month(Table12[Date]),day(Table12[Date]))
Then used that in a Rankx column.
Rank = rankx(filter(Table12,Table12[DateOnly] = EARLIER(Table12[Dateonly])),'Table12'[Date],,,dense)
Not sure if that is what you are after though.
Hello @Greg_Deckler
I was cheking and actually your idea is working but only if I use "Date" as a filter, if I add "id concentrateur" as part if the filter, the formula doesn't work.
How could I fix this problem? i have to apply multiples filters in the rapport, not only date..
thabks in advance.
Hello @Greg_Deckler
Thanks for your reply. Yes, my Date is a real date/time column.
I used your formule, but i have the same result :(, it's picking all results for the measure =0, and nothing when measure =1
Any ideas of this behavior?
thanks in advance.
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |