Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Having trouble creating formula for the Desired_Column. Logic is that "Yes" will show for a Patient_ID with Visit_Type that is not "New Consult" if there is another identical Patient_ID with Visit_Type of "New Consult".
Date | Patient_ID | Visit_Type | Desired_Column |
1/3/23 | 187 | New Consult | |
1/5/23 | 039 | Scan | No |
1/7/23 | 188 | New Consult | |
1/10/23 | 187 | Lab | Yes |
1/16/23 | 039 | Surgery | No |
1/20/23 | 189 | New Consult | |
1/23/23 | 188 | Scan | Yes |
1/25/23 | 102 | Visit | No |
1/25/23 | 190 | New Consult | |
1/28/23 | 190 | Lab | Yes |
1/29/23 | 056 | Scan | No |
1/30/23 | 187 | Surgery | Yes |
Logic examples:
187's Lab visit on 1/10/23 and Surgery visit on 1/30/23 shows "Yes" because they had a "New Consult" visit on 1/3/23.
039's Scan visit on 1/5/23 shows "No" because they have no "New Consult" visit, only a Surgery visit on 1/16/23.
188's Scan visit on 1/23/23 shows "Yes" because they had a "New Consult" visit on 1/7/23.
Link to .pbix file:
https://drive.google.com/file/d/1BvQ8OwRuMjXKTMLHHuTCX7aVcGaxq7J6/view?usp=sharing
Thank you in advance!
Solved! Go to Solution.
@db400 thanks for making this a seperate post, otherwise a lot of people keep on asking the question on the existing post even though it is a new requirement. Kudos to you for following the etiquette to post a question. Thank you!
Well, this is what I think you are looking for:
Column 2 =
VAR __Patient = Patient[Patient_ID]
VAR __Date = Patient[Date]
VAR __Table =
FILTER ( ALLSELECTED ( Patient ), [Patient_ID] = __Patient && Patient[Date] <= __Date && Patient[Visit_Type] = "New Consult" )
RETURN
IF ( Patient[Visit_Type] <> "New Consult", IF ( NOT ISEMPTY ( __Table ), "Yes", "No" ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@db400 one more request, when you reply to the post, make sure to tag the person you are working with.
BTW, you ran into the same issue, the data type of the date column in your file is text, change it to date and it will work.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Works beautifully. Thanks again for your help and also for the post feedback!
@db400 and the link you shared to download the file is not working.
I think if you share sample data and clearly outline the logic, that is sufficient, sharing pbix file surely helps.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@db400 working at my end, I just pasted the data from your post and worked on it, not downloading the pbix file, Maybe there is something different in the file like the previous one:
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
That's strange.. I have tried re-doing the column adding but I am still getting the same problem.
The correct .pbix file link:
https://drive.google.com/file/d/1BvQ8OwRuMjXKTMLHHuTCX7aVcGaxq7J6/view?usp=drive_link
@db400 thanks for making this a seperate post, otherwise a lot of people keep on asking the question on the existing post even though it is a new requirement. Kudos to you for following the etiquette to post a question. Thank you!
Well, this is what I think you are looking for:
Column 2 =
VAR __Patient = Patient[Patient_ID]
VAR __Date = Patient[Date]
VAR __Table =
FILTER ( ALLSELECTED ( Patient ), [Patient_ID] = __Patient && Patient[Date] <= __Date && Patient[Visit_Type] = "New Consult" )
RETURN
IF ( Patient[Visit_Type] <> "New Consult", IF ( NOT ISEMPTY ( __Table ), "Yes", "No" ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks again for your help! I'll be posting quite a few help requests in the future so please let me know if there's anything else I should include in posts to make it easier for those helping.
It seems like the provided formula is not returning the desired result, please see highlighted below.
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |