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 format the data to show the different status's as the columns so I can count them in the final version.

 

I start with this table.

Original Data

ID

Status

Group

Date

1

Staus2

A

04/11/2020

2

Staus2

B

04/11/2020

3

Status1

C

04/11/2020

4

Staus2

A

04/11/2020

5

Status3

A

04/11/2020

6

Staus2

B

04/11/2020

7

Staus2

C

04/11/2020

8

Status1

C

04/11/2020 

9

Staus2

B

04/11/2020

10

Status1

C

04/11/2020

11

Staus2

B

03/11/2020

 

I then format the table to show the status's as the columns. I've tried formatting the table using the initial SQL Query and by using the inbuilt measure within PowerBI. The results of both methods seem to have the same deficit from the original data. 

 

Here is the formula I use to split the data in PowerBI:

Status1 = CALCULATE(
COUNTROWS(Original Data),
SEARCH("Status1",Original Data[Status],1,0))
 
I also tried:
Status2= COUNTX(
FILTER(Original Data,[Status] = "Status2"),
Original Data[Status] )
 

Formatted Data

Group

ID

Status1

Status2

Status3

Date

A

1

0

1

0

04/11/2020 

B

2

0

1

0

04/11/2020 

C

3

1

0

0

04/11/2020 

A

4

0

1

0

04/11/2020 

A

5

0

0

1

04/11/2020 

B

6

0

1

0

04/11/2020 

C

7

0

1

0

04/11/2020 

C

8

1

0

0

04/11/2020 

B

9

0

1

0

04/11/2020

C

10

1

0

0

04/11/2020

B

11

0

1

0

03/11/2020 

 

The final version of the table is the one I wish to use in PowerBI.

 

Final Table

Group

Status1_Total

Status2_Total

Status3_Total

A

0

2

1

B

0

4

0

C

3

1

0

 

The final version of the table is intended to show the total records in each group that are in each status over a period of time (say the last 7 days). 

 

However when I filter the original data by the group, status, and date range within SQL then I get a different count to the data in PowerBI. I'm not sure how to figure out what the problem could be, I originally thought It was out of date data, or that the SQL function BETWEEN worked differently than the PowerBI date range. But this is the first report I've had where the data isn't being counted. the same way.

Any ideas what the possible cause for the totals of the original data being different from the PowerBI version? 

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. 

8 Replies

Replies have been turned off for this discussion
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      That's a much smarter solution than what I had, thank you. (you can tell I'm rather new to PowerBI)

       

      Sadly I have a couple of calculated fields hidden away as columns in my version of the report, I'm not sure I would be able to add them on using the matrix.

      But as clunky as my version is it, it does work with the table.   

      My main issue is that I can't figure out why I have a data mismatch between the database and PowerBI. The data I'm using in SQL is correct but power BI appears to be missing some of my records. 

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The SQL database holds the correct data. But for some reason, PowerBI isn't returning all the information.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      It was just me being an idiot. PowerBI was working as intended but I used the minimum date when I grouped the data to compare it. But when I used the between clause in the query the results are different due it the database ignoring previous events. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.