Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to use the entry with data on matrixes

So I have a dataset with a list of people who are in a hospital, and it is updated daily. One of the things I need to work with is their weight, which is divided into sections (Column name WTRank), and Population is a DISTINCTCOUNT() formula on the column that contains their IDs. If I put the two of them together in a Matrix, WTRank in Rows and Population in Values, I get this as a result:

 

 

 

 

 

 

Now, last time I checked, 12 + 7 + 7 + 1 does not make 18. However, I know why this happens. There are 18 unique IDs in Population, and if I put the IDs in Rows as well, I get to see that some IDs are repeated (I put numbers on said IDs).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If I open Power Query, I can see why this is happening. Not every ID is fully audited every day or has available info on the day the information is captured, so it's kept in blank, therefore why it appears twice on the matrix (Every row this ID appears in is a day that passes).

 

 

 

 

 

 

So my question is: How do I prevent my IDs from coming up repeatedly in a matrix? How do I tell it to only take the ID when it actually has data, and at the same time list it as "NA" if the ID has no data whatsoever?

 

Here's my sample data and the Power BI file that I made to show this example. Thank you for the assistance.

Need Help

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    The visual you are creating will make sense only when you select a day in the Date slicer.  This is because the categories that people fall into will/may vary day on day.  So you should create a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  Create a slicer from the Date column of the Calendar Table and select a certain date.

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey, thank you for stopping by!

       

      That suggestion is actually what I already have on my main Power BI sheet that has more data from more months. It works well if I want to show a daily status, but my client also wants to see a monthly report, quarterly report, or just any range in particular, so I have to keep the slider with the range of dates, while also making sure the data is relevant and not the mistake I'm making above.

       

      If a matrix won't work for what I'm looking for, do you have any similar visuals or methods in mind? They could come from an outside source, it doesn't really matter much.