Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
shrimankabra
Frequent Visitor

Fixed values for a measures as a column in a matrix / table.

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.

1 ACCEPTED 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.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@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, 

Capture.PNG

 

Now when I add Hospital column to it,

Capture1.PNG

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,

Capture2.PNG

 

As you suggested before I have changed the measure, but still not getting what I want. Here's what I get instead,

Capture3.PNG

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.