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...
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.

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