Forum Discussion

Changlin's avatar
Changlin
Frequent Visitor
4 years ago
Solved

Merged Table data not displaying data correctly in chart

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      Changlin
      Frequent Visitor
      4 years ago

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Changlin
      Frequent Visitor
      4 years ago

      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