Table Drill Down Includes Unrelated Records

[bdanielx]

When I drill down on row in a Matrix, I get the detail that I want, but also a bunch of other records unrelated to the drill-down field in the result. Is this a data relationship issue, or is there something else I am missing?

Here is the higher level

Here is the drill down (note unrelated items at the bottom of the matrix):

[Ashish_Mathur]

Hi,

Looks like a relatioship issue.  Share the link from where i can download your PBI file.

[bdanielx]

Thanks. Here it is.

https://drive.google.com/open?id=1LHRtxlKP_wJTupsbyujZhMJVxlMpIWNa

The matrix is on the Sales Detail page.

[Ashish_Mathur · Accepted solution]

Hi,

The reason you have rows showing with no sales data is that there are two growth columns in that visual which are returing 100%.  Try these revised messures and those rows will disappear

TY Sales % Comp = IF(AND(ISBLANK(SUM('Sales By Location'[TY Sales])),ISBLANK(SUM('Sales By Location'[LY Sales]))),BLANK(),SUM('Sales By Location'[TY Sales])/sum('Sales By Location'[LY Sales])-1)

TY Sales Unit % Comp = IF(AND(ISBLANK(SUM('Sales By Location'[TY Sales Units])),ISBLANK(SUM('Sales By Location'[LY Sales Units]))),BLANK(),(sum('Sales By Location'[TY Sales Units])/sum('Sales By Location'[LY Sales Units]))-1)

Hope this helps.

[bdanielx]

Yes, that solved my issue! Thank you.

But, I don't understand why the other items would show on the drill down when there is no data relationship to the drill down field?

Also, by making these lines blank in the conditional statement, why don't the items with the blank result still show in the report with just a blank field?

[Ashish_Mathur]

You are welcome.  I do not understand the meaning of your other 2 questions.

[bdanielx]

Well, the drill down field was the sub-category group 2.00-QT Poinsettia, but their is no database relation between the extra items without data that displayed in the table and the 2.00-QT Poinsettia sub-category in the data tables, that is, those items belong to different sub-category group. I would assume that since they are related to a different sub-category group that they would be excluded from the results when I drill down.