Forum Discussion

iWonder's avatar
iWonder
Helper I
3 years ago
Solved

New Measure Creates Blank Row

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!
Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    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.

15 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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's avatar
      iWonder
      Helper I
      3 years ago

      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

    • iWonder's avatar
      iWonder
      Helper I
      3 years ago

      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

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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.

    • iWonder's avatar
      iWonder
      Helper I
      3 years ago

      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's avatar
    parry2k
    Super User
    3 years ago

    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's avatar
      iWonder
      Helper I
      3 years ago

      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

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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.

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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's avatar
      iWonder
      Helper I
      3 years ago

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

       

      I really appreciate  your help.