Missing Data Rows

[Anonymous]

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? 

[Anonymous · Accepted solution]

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. 

[HotChilli]

This is confusing.  What data is missing?

[Anonymous]

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. 

[mahoneypat]

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

[HotChilli]

Which version is correct: PowerBi or SQL?

[Anonymous]

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

[camargos88]

@Anonymous ,

Check the attached file.

[Anonymous]

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.