Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Missing Data Rows

Hi All,   I am trying to create a table in PowerBI that shows the total records that are in each status split by the group they are in. I originally take the data from an SQL Database. Then I forma...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi all,

    Thank you so much for your help, and showing me the matrix and that date slider 🙂 

     

    I figured out what my issue was. It was in the SQL.

    SELECT ID, Status, Group, MIN(Date)
    FROM Table
    WHERE Date
    BETWEEN '2020-10-30' AND '2020-11-05'
    AND Group= 'A'
    AND Status = 'Staus1'
    Group By Group,Status, ID
    ORDER BY MIN(Date) DESC

    It's because I used the between clause to compare the data. Obviously, If I ignore everything before that date the minimum date changes. So that number will be higher than the numbers in the report. 

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Your original data table is perfectly formatted for analysis with DAX and visuals, and you should not need to transform it further to get your desired table.  Just create a matrix visual with Status column on columns, Group column and rows and drag any column into the values and choose a count aggregation.  If that is not exactly right, I'm sure a measure can be written to get what you need.

Regards,

Pat