Drilldown to detail Tab to display rows in table B not in table A-based on one common column

[Anonymous]

Drilldown to detail Tab to display rows in table B not in table A-based on one common column check produces wrong result.

I have this report referencing two tables: 

consumerExpenseCodes  &

producerConsumerExpenseCodes

ConsumerExpenseCodes  contains ALL expense codes.  

There are some expense codes in ConsumerExpenseCodes table which are not in producerConsumerExpenseCodes .

The following report count of distinct expensecodes in each table:

For each consumerName

Expense codes used by firm = expensecodes in producerConsumerExpenseCodes

Expense codes used by all firms = expensecodes in ConsumerExpenseCodes

Total potentialExpense codes = expensecodes in ConsumerExpenseCodes   but not in producerConsumerExpenseCodes

The challenge is to right-click on row Consumername and drill fown to a detail report list of all expensecode that belong to Total potentialExpense codes (7 in this case).   

Issue:  The requirement is to be able to drill down to "Detail - potential codes" page and see a list of expense codes that make up column "Potential difference".   Try drill down on consumer Accident Funded.  

PBIX link:  https://1drv.ms/u/s!AlMdRxAveLesgcFbY-CNnOQsTx8KjQ?e=EOIlL6

Observe the following issues:

1.  A consumer must have a confidence score of more than 500 to qualify to be in report. Added to filter pane. 

2.  Total in table is not adding up.  Also, total summary cards are only correct when you cross-filter by consumer name

3.  When you take out the ProducerId filter the detail page is messed up.  

I am following your solution on  Solved: Select rows not present in related table using a m... - Microsoft Power BI Community

Ii is not working.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

Except Codes Measure =
VAR Code_ =
    MAX ( 'report consumerExpenseCodes'[expenseCode] )
VAR producerCodes_ =
    VALUES ( 'report producerConsumerExpenseCodes'[expenseCode] )
VAR consumerCodes_ =
    VALUES ( 'report consumerExpenseCodes'[expenseCode] )
VAR Except_ =
    EXCEPT ( consumerCodes_, producerCodes_ )
RETURN
    IF ( Code_ IN Except_, 1 )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey 

Thanks.  This dax works for the drill down column.  

Please observe what happend when you take out the producerId filter. 

The table columns are duplicated.  How can I fix this?   

[Anonymous]

I have this simple model:

Table report consumerExpensecodes (Table B) contains all rows (expensesCode column) of report producerConsumerExpenseCodes (Table A).      Tables are joined based  on the highlighted columns.

Problem: Based on consumerName, I need to create a report of (4colunms):  consumername, count of expenseCodes on both  Tables A & B and potentail difference.  

This is working well for the counts - Master report.  No issues.  

The challenge is to drill down on consumerName to extract detials of rows that make up the difference "Total potrentialExpense Codes".   That means expenseCodes in B but not A. 

I found and applied this solution online:  Solved: Re: DAX query to compare a value in one table to s... - Microsoft Power BI Community

Baed on that I created the following new column in  table (B)  report consumerExpensecodes

*Matched Column =

CALCULATE( COUNTROWS('report producerConsumerExpenseCodes'), FILTER('report producerConsumerExpenseCodes', 'report producerConsumerExpenseCodes'[ConsumerIdExpenseCode] = EARLIER('report consumerExpenseCodes'[ConsumerIdExpenseCode])) ) > 0

Result:  Unfortunately, This is returning True for all rows.   

There are definitely False situations.  I need urgent help, 🙂 

What am I doing wrong ?

[Anonymous]

This ticket is inadvertently duplicated and has been updated with sample PBIX as per:

Re: Drilldown to detail Tab to display rows in tab... - Microsoft Power BI Community

[Anonymous]

@Icey 

Thank you. I have tried to keep things simple.   Please find the PBIX attached. 

https://1drv.ms/u/s!AlMdRxAveLesgcFbY-CNnOQsTx8KjQ?e=EOIlL6

Issue:  The intention is to be able to drill down to "Detail - potential codes" page and see a list of expense codes that make up column "Potential difference".   Try drill down on consumer Accident Funded.  

Observe the following:

1.  A consumer must have a confidence score of more than 500 to qualify to be in report. Added to filter pane. 

2.  When you take out the ProducerId filter the detail page is messed up.  

[Icey]

Hi @Anonymous ,

Could you create a sample .pbix file with the same issue for us to test? Please remove sensitive information and unnecessary parts.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey