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 everyone,
In Power BI I'm trying to show the earliest date per ID.
As an example, if the ValidFrom date slicer is set from 1-5-2020 to 1-12-2020, and a slicer for ID set to ID="1", the table visual should show only ID 1 with date 1-5-2020.
Sample data:
ID ValidFrom
1 1-1-2020
1 1-5-2020
2 1-1-2019
2 1-1-2019
3 1-1-2019
What would be a way to achieve this in Power BI?
Thanks!
Solved! Go to Solution.
Hi, @GKJARC
According to your description, I think you can easily create a measure then use it in filter pane to filter the desired results.
Like this:
Measure =
VAR a =
MINX (
FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) ),
[ValidFrom ]
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[ValidFrom ] )
IN DISTINCT ( 'Table 2'[Date] )
&& SELECTEDVALUE ( 'Table'[ValidFrom ] ) = a,
1,
0
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @GKJARC
According to your description, I think you can easily create a measure then use it in filter pane to filter the desired results.
Like this:
Measure =
VAR a =
MINX (
FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) ),
[ValidFrom ]
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[ValidFrom ] )
IN DISTINCT ( 'Table 2'[Date] )
&& SELECTEDVALUE ( 'Table'[ValidFrom ] ) = a,
1,
0
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi, @GKJARC
Please check the below picture and the sample pbix file's link down below.
Earliest Valid Date =
IF (
ISFILTERED ( IDs[ID] ),
CALCULATE (
MIN ( 'Table'[ValidFrom] ),
FILTER ( ALLSELECTED ( Dates ), Dates[Date] >= MIN ( Dates[Date] ) )
)
)
https://www.dropbox.com/s/5r5fidugk3q09wm/gkjarc.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
what is the table structure and relationships? or is this one table?
earliestdate =
VAR selectedid =
SELECTEDVALUE ( id )
RETURN
CALCULATE ( MIN ( validfrom ), id = selectedid )
Proud to be a Super User!
@GKJARC , Try a measure like
measure =
var _min = minx(allselected(Table), Table[ValidFrom])
return
calculate(min(Table[ID]), filter(Table, Table[ValidFrom] =_min))
Or plot this with ID column in visual
measure =
var _min = minx(allselected(Table), Table[ValidFrom])
return
calculate(count(Table[ID]), filter(Table, Table[ValidFrom] =_min))
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |