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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.