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.
I have one table with id, first day, last day, visit type and I want to make a calculated column (last day - first day) which shows the minimum for each visit regardless of the visit types chosen
So I have this so far:
visit id | first date | last date | visit type | days between(calculated column) |
100 | 1/1/2018 | 1/8/2018 | Session | 7 |
100 | 1/2/2018 | 1/10/2018 | Orientation | 8 |
101 | 1/3/2018 | 1/15/2018 | Session | 12 |
101 | 1/4/2018 | 1/11/2018 | Peer | 7 |
102 | 1/5/2018 | 1/23/2018 | Peer | 18 |
103 | 1/6/2018 | 1/23/2018 | Group | 17 |
103 | 1/7/2018 | 2/14/2018 | Individual | 38 |
103 | 1/8/2018 | 1/16/2018 | Peer | 8
|
But I want to only show
visit id | first date | last date | visit type | days between |
100 | 1/1/2018 | 1/8/2018 | Session | 7 |
101 | 1/4/2018 | 1/11/2018 | Peer | 7 |
102 | 1/5/2018 | 1/23/2018 | Peer | 18 |
103 | 1/8/2018 | 1/16/2018 | Peer | 8 |
So basically, the end user can select any visit type and the file will find the least days between date and visit type and show that only.
Solved! Go to Solution.
HI @saanah2019 ,
You can consider to create a measure to compare with current row content if it is last records and return tag. You can use this on visual level filter to filter unmatched records.
First Records = VAR currlast = MAX ( T2[last date] ) VAR _last = CALCULATE ( MIN ( T2[last date] ), ALLSELECTED ( t2 ), VALUES ( T2[visit id] ) ) RETURN IF ( currlast = _last, "Y", "N" )
Regards,
XIaoxin Sheng
HI @saanah2019 ,
You can consider to create a measure to compare with current row content if it is last records and return tag. You can use this on visual level filter to filter unmatched records.
First Records = VAR currlast = MAX ( T2[last date] ) VAR _last = CALCULATE ( MIN ( T2[last date] ), ALLSELECTED ( t2 ), VALUES ( T2[visit id] ) ) RETURN IF ( currlast = _last, "Y", "N" )
Regards,
XIaoxin Sheng
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |