Merged Table data not displaying data correctly in chart

[Changlin]

Hi,

I have merged 2 tables A and B with left join in Power BI. Table A has 355 rows (Contains ID, Name columns) and Table B (Contains ID, State and Created Date columns) has thousands rows. I got merged table that I want with 355 rows (Contains ID, Name, State and Created Date columns). I need to display chart like the following picture in Power BI.

But when I using Area chart to display the data:

Axis - Merged table Created Date 

Legend - Merged table State

Values - Merged table Sum of ID

I got the following chart

The value is totally incorrect cuz my merged table should only have 355 rows. I tested and searched a lot but still can't fix this. I don't know why it shows record of table B but not the merged table.

Please help me!

Thanks,

Changlin

[Anonymous · Accepted solution]

Hi @Changlin ,

Please try:

Count = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[State]=MAX('Table'[State]) && [Date]<=MAX('Table'[Date])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Changlin]

Hi Eyelyn,

Thanks for your answer, your query helps me solve the problem. I have marked you answer as solution.

Have a good day,

Changlin

[parry2k]

@Changlin very hard to tell, I guess you still have 355 rows in your main table, or has the number of rows increased? Can you share pbix file, and remove sensitive information before sharing. You can share using one drive/google drive.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[Changlin]

Hi parry2K,

Thanks for answering, I just found that the number is huge because it sums the ID (which is whole number), so that's a mistake. 

I made a simple template table as the following:

The truly problem is how to count rows before each row Date for 2 different states.

For example:

It should looks like this, can I build a query to get this count column?

Thanks,

Changlin