Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kevin_Harper
Helper I
Helper I

Bogus rows being created

Oi,

I have a problem with a table visual in pbi desktop showing more rows than necessary and relating bogus data to those rows that I can't see how they are making any relationship.

 

In the following visual, I have 2 tables and 2 slicers.  I showed the planner table so you can see everything that is in it which is just 3 records.  I have a 1 to many relationship from the planner table to the DH Goals 2019 table on planner.

 

Everything except the total is calculating correctly in this scenario, but I need to add the planners column to this table visual.

 

wo Planner.jpg

 

When I add 'Planners'[planners] to the visual, a bunch of new rows appear that tie every planner to every region.  Of course, there is no data for most planners because there is only 1 planner to each region.  For example starting on the second row, there is planner sparker tied to region CN, but I don't have any data in any table that ties sparker to CN:

w planner.jpg

Here is a sample of the data in my DH Goals table:

sample dh goals table.jpg

I am thinking there is something to do with the 'DH Goals 2019' table but all the rows in there are unique.  Does anyone have any ideas what might be causing this?  I am grateful for any power bi ninja's help.

 

1 ACCEPTED SOLUTION

Hi @Kevin_Harper ,

 

Please modify the formula of measure [dh pay] as below:

dh pay = IF([Empty %]=BLANK(),BLANK(),
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "CN"))> Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "CR")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "GL")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "MW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay sparker],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "NE")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "NW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "RM")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "SE")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "SW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "TX")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "UP")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor], 0))))))))))))

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

Hi @Kevin_Harper ,

 

I was not able to reproduce the problem on my side. Would you please share the sample .pbix file for further check? Please mask sensitive data before sharing.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Yuliana,

Thank you for your time and assitance with this.  Sorry for the delay as I was having trouble figuring out how to add an attachment.

 

Planner Report

 

I uploaded it to google drive and shared the link.  Hopefully this ok.

I think I had the wrong link posted but it should be correct now.

 

Looking at this more, simply selecting planners is causing a cross join for some reason.   I'm not sure why bi would do that.

Hi @Kevin_Harper ,

 

Please modify the formula of measure [dh pay] as below:

dh pay = IF([Empty %]=BLANK(),BLANK(),
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "CN"))> Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "CR")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "GL")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "MW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay sparker],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "NE")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "NW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "RM")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "SE")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "SW")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "TX")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay bwhite],
IF(CALCULATE(SUM('DH Goals 2019'[Goal]), FILTER(Region,Region[Region] = "UP")) > Deadhead[Empty %], 'DH Goals 2019'[Region pay tmcgregor], 0))))))))))))

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You're brilliant, that worked!  So just allow for the extra data to calculate, but filter it out by using the blank() function and now only valid rows show.  Thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.