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 am trying to show a field value (which is a score rating) for multiple appointments for the same case.
So for example contact A has 5 appointments logged for Case1 and the first appointment was on the 1/01/2021 and the last appointment was on hte 28/02/2021.
the field for the score rating sits within the appointment table and only 3 appointments have a score.
How can I upll the data to show the first appointment date (with a score) and he last appointment date (with a score)?
Help is always appreciated
Solved! Go to Solution.
Hi @James_Ma ,
Create 2 measures as below:
The first date =
VAR _mindate =
CALCULATE (
MIN ( 'Table'[Date of Assessment] ),
FILTER (
ALL ( 'Table' ),
'Table'[Case ID] = MAX ( 'Table'[Case ID] )
&& 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
&& (
'Table'[Q1 score] <> BLANK ()
|| 'Table'[Q2 score] <> BLANK ()
)
)
)
RETURN
_mindate
The last date =
VAR _maxdate =
CALCULATE (
MAX ( 'Table'[Date of Assessment] ),
FILTER (
ALL ( 'Table' ),
'Table'[Case ID] = MAX ( 'Table'[Case ID] )
&& 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
&& (
'Table'[Q1 score] <> BLANK ()
|| 'Table'[Q2 score] <> BLANK ()
)
)
)
RETURN
_maxdate
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @James_Ma - If this is still an issue for you, can you please post some sample data? It should be relatively simple to solve.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi KNP,
Thanks for your response, in reality a DAX expression would be perfect, I have a small data file which has hte relevant fields and I just want the Meaasure to pull the first date that has a score and hte last date that has a score.
Date of Assessment | Case ID | Full Name | Assessments ID | Q1 score | Q2 score |
07/06/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 02cd84aa-aec7-eb11-bacc-000d3a86ef21 | 17 | 7 |
30/04/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 0b13cdca-c8a9-eb11-9442-002248001f1f | 19 | 21 |
04/06/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 0e3452b2-bebf-eb11-bacc-0022481a610c | ||
13/08/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 3cb46205-23fc-eb11-94ef-0022481b495a | 19 | 17 |
07/05/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 4c764874-6fae-eb11-8236-002248005e20 | 21 | 19 |
14/05/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 72a7768f-4daf-eb11-8236-0022480050a8 | ||
25/06/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | 7d56baa3-95d5-eb11-bacb-0022481aaa55 | 20 | 17 |
18/06/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | c255e553-aec7-eb11-bacc-000d3a86ef21 | 17 | 7 |
28/05/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | d74e4662-66bc-eb11-bacc-0022481a6e41 | 17 | 7 |
21/05/2021 00:00 | e0c41db5-1219-4794-ae4f-680f409e3a59 | Contact 1 | fb585a25-52b9-eb11-8236-00224800ee20 | 19 | 19 |
Hi @James_Ma,
Sorry I missed your reply.
If you're happy with a DAX expression then I suspect @Greg_Deckler's answer will work. If you still want Power Query then I'll have a look for you.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @James_Ma ,
Create 2 measures as below:
The first date =
VAR _mindate =
CALCULATE (
MIN ( 'Table'[Date of Assessment] ),
FILTER (
ALL ( 'Table' ),
'Table'[Case ID] = MAX ( 'Table'[Case ID] )
&& 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
&& (
'Table'[Q1 score] <> BLANK ()
|| 'Table'[Q2 score] <> BLANK ()
)
)
)
RETURN
_mindate
The last date =
VAR _maxdate =
CALCULATE (
MAX ( 'Table'[Date of Assessment] ),
FILTER (
ALL ( 'Table' ),
'Table'[Case ID] = MAX ( 'Table'[Case ID] )
&& 'Table'[Full Name] = MAX ( 'Table'[Full Name] )
&& (
'Table'[Q1 score] <> BLANK ()
|| 'Table'[Q2 score] <> BLANK ()
)
)
)
RETURN
_maxdate
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@James_Ma This is Lookup Min/Max:
Lookup Max Simple =
VAR __Table = 'Table'
VAR __Max = MAXX(__Table,[Date])
RETURN
MAXX(FILTER(__Table,[Date] = __Max),[Column])
Lookup Min Simple =
VAR __Table = 'Table'
VAR __Min = MINX(__Table,[Date])
RETURN
MAXX(FILTER(__Table,[Date] = __Min),[Column])
Lookup Min/Max - Microsoft Power BI Community
And now I just realized that you want Power Query and this is a DAX solution. Oh well, if you are able to do it in DAX this is how. No idea in Power Query.
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |