Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
In my data set, I have set of operations and set of hospitals in a single table. There are number of hospitals which performs a particular operation. For example, Hospitals (a, b, c, d, e) performs Operation "p1" and Hospitals (a,d,i,j,k,l,m) performs Operation "p2". Hence, Hospital count for Operation "p1" becomes (a, b, c, d, e) i.e. 5 and Hospital count for Operation "p2" becomes (a,d,i,j,k,l,m) i.e. 7.
Using this data, I have created a matrix and added Operations column to the rows field of the matrix and created a measure "Hospital Count" as
Hospital Count = CALCULATE(DISTINCTCOUNT(PATIENT_JOURNEY_2[HOSPITAL_NAME]))
and added it to the value field of the matrix which gives me count of hospitals for each operation.
But, now when I add Hospitals column to the matrix in rows field after Operations, the measure values changes and gives count 1 for each Hospital per Operation.
Please help me modify above measure (or any other way) to make the count fixed for each operation even after adding Hospitals column. That means, instead of showing count as "1" infornt each hospital, if there are seven hospitals under one operation the count should show "7" infornt of each of those seven hospitals under that particular operation.
Thank You.
Solved! Go to Solution.
@shrimankabra oops there was mistake on my end when I gave you the dax
Hospital Count = CALCULATE(DISTINCTCOUNT(PATIENT_JOURNEY_2[HOSPITAL_NAME]), ALLEXCEPT(PATIENT_JOURNEY_2,PATIENT_JOURNEY_2[Operation]))
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.
@shrimankabra isn't that will look wrong, if I understood this is wht you what
Operation Hospital Count
O1 a 7
O1 d 7
O1 i 7
O1 j 7
O1 k 7
O1 l 7
O1 m 7
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.
Yes, to me more precise, here is the sample,
Operation Hospital Count
o1 a 5
o1 b 5
o1 c 5
o1 d 5
o1 e 5
o2 b 3
o2 k 3
o2 l 3
o3 a 4
o3 c 4
o3 d 4
@shrimankabra try following measure
Hospital Count = CALCULATE(DISTINCTCOUNT(PATIENT_JOURNEY_2[HOSPITAL_NAME]), ALLEXCEPT(PATIENT_JOURNEY_2,PATIENT_JOURNEY_2[HOSPITAL_NAME]))
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.
Doesn't works.
@shrimankabra can you share sample data and also if you can answer to tell what doesn't work, it will help, are you getting wrong result? error in expression or no change?
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.
Here is the sample data with the dax measure,
Now when I add Hospital column to it,
the count values in this case is absolutely correct, but I want the values of previous image in here instead of this as shown below,
As you suggested before I have changed the measure, but still not getting what I want. Here's what I get instead,
I hope my requirement is clear now.
Thanks.
@shrimankabra oops there was mistake on my end when I gave you the dax
Hospital Count = CALCULATE(DISTINCTCOUNT(PATIENT_JOURNEY_2[HOSPITAL_NAME]), ALLEXCEPT(PATIENT_JOURNEY_2,PATIENT_JOURNEY_2[Operation]))
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.
**Please ignore the previous reply**
Yes, to me more precise, here is the sample,
Operation Hospital Count
o1 a 5
o1 b 5
o1 c 5
o1 d 5
o1 e 5
o2 b 3
o2 k 3
o2 l 3
o3 a 4
o3 c 4
o3 d 4
o3 f 4
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |