New Measure Creates Blank Row

[iWonder]

Thank you for taking the time to read my question.

my table QuoteHeader has 3 records in it with related records in QuoteDetail. I want to check if all rows are "Ordered" (Ordered =1, Not Ordered = 0). So if I count the rows and compare it to the SUM() of OrderPlaced then I will know which ones are completely filled.

This is my formula: 

OrderComplete = If(Count(cra98_quotedetail[tKey]) = Sum(cra98_quotedetail[OrderPlaced]),"Yes","No")

I created a new Measure in my file and when I add it to my Table Visual it adds an empty row giving me 4 rows instead of 3. The formula/meausre returns "Yes" because a count of zero equals a sum of zero. The rest of the Table Visual columns for that row are empty. All QuoteDetail rows relate to a QuoteHeader record.

Why is this happening?

For now I'm filtering out any rows with a blank Created On date.

Not sure if it helps but my data is coming from Dataverse.

Thanks!

[parry2k]

@iWonder Good to know. Cheers!!

[parry2k · Accepted solution]

@iWonder well that makes sense, basically, you are telling it that there is integrity between the tables because for DQ it doesn't pull all the data and by selecting that check box you are basically confirming the referential integrity. I hope it makes sense.

✨ 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!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[iWonder]

It does! Thank you very much. This is the first time I created a Direct Query report.

I really appreciate  your help.

[parry2k]

@iWonder are you using direct query (DQ) ?

[iWonder]

Yes

[parry2k]

@iWonder what if you uncheck it, does the blank row shows up again?

✨ 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!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[iWonder]

Yes

[parry2k]

@iWonder good to know but I will be still curious why the blank condition is required 🤷‍♂️

✨ 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!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[iWonder]

I'm going to clear all my data and add new data one at a time and check to see what's going on. I can post back

[iWonder]

I will have to clean tomorrow.

have a good one

[iWonder]

Hi @parry2k 

I figured it out. Here is my formula:

OrderComplete = IF(ISBLANK(Count(cra98_quotedetail[tKey])),BLANK(),If(Count(cra98_quotedetail[tKey]) = Sum(cra98_quotedetail[OrderPlaced]),"Yes","No"))

Now I don't get a blank row.

thanks!

[parry2k]

@iWonder interesting, can you share the pbix file using one/google drive, remove sensitive information before sharing. 

✨ 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 efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@iWonder this will explain why it is happening, Why is there a BLANK in my Power BI slicer??? - Guy in a Cube

✨ 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 efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[iWonder]

Hi @parry2k 

after playing for a LONG time I tried checking the "Assume referential integrity" check box in the Edit Relationship box and that took away my blank row.

I've never checked this before. Any idea why this helped?

thanks

[iWonder]

Hi @parry2k 

Thank you for the quick reply.

I followed the video, I also put my data into Excel (only 3 header rows and 19 detail rows) and everything has a related record.

Not clear on what I should try next

thanks