duplicating rows in matrix/ grid -when selecting "don't summarize"

[heathernicole]

duplicating rows in blue outline

I am trying to create a simple grid that looks at line items from a PO. 

When I select a "Don't Summarize" to the Line Rate (or any field for that matter), it starts duplicating rows.

If I don't do that - it summarizes the data - which I don't want. 

What should occur is for each PO - there a Design # (can be multiple) and for each design # - there will be a line rate and a quantity. But I don't know what's causing it to duplicate over and over again. 

For instance - the first blue outlined section in the grid (at the top) - should ONLY display one row for each design number (125557 and 125558). Those two design numbers should not show twice.

I'm wondering if part of the reason is because the design number field is a text data type. Occasionally there will be letters in the design #. 

Any pointers in the right direction would be exceptionally helpful. 🙂

[vanessafvg]

@heathernicole

hi heather unless i am missing something, its going to duplicate becase line rate and quantity printed is different?  youve told it not to aggregate so each line item will be present,.

[vanessafvg]

@heathernicole is this all in the same table, or do you have them linked in a relationship?

[heathernicole]

@vanessafvg - 

not the same table. The entire dataset probably has about 4-6 tables total - off the top of my head. 

[heathernicole]

@vanessafvg - hey! 🙂

Well, that's what I can't seem to figure out. On the PO -it's correct. There's no duplicate information.

If I allow it to summarize - adds up the line rates and duplicates that for each design number and the same for the quantity.

So for instance - I have the following in reality:

Design #       QTY       Line Rate

12345           100          .50

12346           100          .75

12347           100          .50

When I pull it into Power BI it's doing this: (If I let it summarize)

Design #       QTY       Line Rate

12345           300          1.75

12346           300          1.75

12347           300          1.75

If I don't let it summarize:

Design #       QTY       Line Rate

12345           100          .50

12346           100          .75

12347           100          .50

12345           100          .50

12346           100          .75

12347           100          .50

12345           100          .50

12346           100          .75

12347           100          .50

Neither of those is right. However the first instance (of what's actually on the PO) is what I'm wanting. If those three lines were on the same PO - the PO# would be the same for all - and that's ok. 

[v-chuncz-msft]

@heathernicole,

I agree with the comment above. You could also consider using DAX to add the result table.

[heathernicole]

@v-chuncz-msft - I've been digging through on how I would use DAX to solve this one - I haven't had an "aha" moment yet. 🙂 

[vanessafvg · Accepted solution]

@heathernicole  the issue is defintely your relationships, if you could post your pbix that woudl be most ideal, if not can you post a picture of your relationship view identifying the problem data

[heathernicole]

@vanessafvg - Hello! 🙂

You're most definitely right! I had that hunch yesterday morning - but not sure WHICH relationships that would cause it. 

I switched to a different base report I thought might have the proper relationships (that I would need) and got it to work. I just hadn't had a chance to post. I'm going to try and investigate a little further - so I can give a clear answer.

But thank you! You were right! 🙂 

[Anonymous]

@heathernicole did you ever find a solution to this problem?

i'm currently working through the exact same issue, though i'm only working with a single SQL query. any posts, dax code or methods you used to get the outcome you desired would be helpful! 

thanks,