Measure ignore drill down context

[lomorris]

I have a drill down page in my BI report that works great, but I need to add a count measure. When I add the count measure it seems to ignore the context/evaluation and brings back results from the whole table, thus adding rows that don’t apply.

The first screenshot is of the report page “Forklift Invoice Summary” that is a drill down of a list of invoices from NEQ General Ledger Entries Revenue and NEQ General Ledger COS tables. If you right click on the row it drills down to the “Invoice Details” Page and you get the details of line items for that invoice. That dat comes from the "Invoice Transactions table. What I am trying to do is to add a count column the Forklift Invoice Summary page from data pulled from the invoice transaction table that have a product posting group value of “FOR”.

You drill down from the page above and you see the details. The first two rows on the Invoice Details page are the forklift line items of which I want to count. if you look in the invoice transactions table by document number you will see they have a product posting group value of “FOR”.

My truck count measure
truck count = CALCULATE(COUNTROWS(VALUES(‘Invoice Transactions’[Prod Posting Group])))

Below is the screenshot of the results when I add the truck measure. It counts just as I want but ignores the context and returns results for the whole transaction table. I want it to only provide counts for the list of invoices from the first screenshot.

I have attached my pbix file.
GP Report 06172022.pbix (1.3 MB)

[pranamg]

I notice that the Customer Number and Customer Name in the matrix table originate from ‘Documents’[Sell to Customer Number] and ‘Documents’[Sell to Customer Name] columns. If you can add a filter to remove blanks from ‘Sell to Customer Number’ in Power Query, the phantom Document numbers without Customer Number, Customer Name and any other details will no longer appear in the drill down table. This in turn means you will not get count rows for those phantom rows.

[lomorris]

Pranagm,
I may be misunderstanding your solution. Are you saying that I should go to Transform and for the documents table set a filter to remove the customer number that is blank or null?. If that is what you are suggesting, that will cause an issue in the I do need to account for the Document numbers that don’t have a customer number or name. The have a dollar value that is found through the data model. What I did do was to add a two custom columns that now display the value of “Credit memo”. This actually was something that helps the user. Unfortunately I still have the same issue. Maybe I am missing the point you were making.

[pranamg]

Thanks @lomorris for your clarification. I wanted to understand if the Document numbers that don’t have a customer number or name had any $ value associated with them. Can you try the below update to the truck count DAX Measure and let me know if this solved your issue.

truck count =
CALCULATE (
    COUNTROWS ( VALUES ( 'Invoice Transactions'[Prod Posting Group] ) ),
    FILTER (
        'NEQ General Ledger Entries Revenue',
        'NEQ General Ledger Entries Revenue'[General Product Posting Group] = "FOR"
    )
)

Also, I notice Auto-Date Table in your model. You might want to disable auto-date from Options Settings under Current File. And also mark the Date column and Dates Table as Date Column/Table. You might also want to run the Data Model through Best Practice Analyzer from Tabular Editor (External Tools) to improve the Model Performance.

[lomorris]

Thanks for the response pranamg,
Your approach did handle the the issue of not ignoring the context, but the count was incorrect. Below are screenshots for an example. The invoice summary shows a count of 1, the invoice details show three items that have a product posting equal to “FOR”

I will also take your suggestions related to the date table and the model analyzer.

[pranamg]

I assumed unique Document Number. But now I realize, it is the count of unique Serial Number. Please try out this updated formula.

truck count =
CALCULATE (
    COUNTROWS ( VALUES ( 'Invoice Transactions'[Serial Number] ) ),
    FILTER (
        'NEQ General Ledger Entries Revenue',
        'NEQ General Ledger Entries Revenue'[General Product Posting Group] = "FOR"
    )
)

[lomorris]

pranamg, almost there. The count now is equal all the items in the invoice. The example below should have a count of 2, but has a count of six.

[pranamg]

What is the criteria for truck count here? Which rows in the above screenshot should actually be counted and why?

[pranamg]

@pranamg

truck count =
CALCULATE (
    COUNTROWS ( VALUES ( 'Invoice Transactions'[Serial Number] ) ),
    FILTER (
        'NEQ General Ledger Entries Revenue',
        'NEQ General Ledger Entries Revenue'[General Product Posting Group] = "FOR"
    )
)

I changed the filter criteria below so the truck count is 2 and not 6 in your above example. But the filtered rows are not matching and you now have rows with Gross Profit = 0 which will need to be filtered out. I have used a Visual Level Filter to remove any rows where the Forklift Gross Profit is not blank.

Similarly, I added a Visual Level Filter on Invoice Details table such that Gross Profit is not 0.

truck count = 
CALCULATE (
    COUNTROWS ( VALUES ( 'Invoice Transactions'[Serial Number] ) ),
    FILTER (
        'Invoice Transactions',
        'Invoice Transactions'[Prod Posting Group] = "FOR"
    )
)

[lomorris]

Thanks for working with me on this. The first to rows are truck and have a Product posting group value of FOR. the remaining 4 rows are accessories and have a Product Posting Group value of ACC. The goal is to count the trucks only

[lomorris]

Gotcha, I tried that too, but the user will want to see all details of the invoice, so the invoice details page will need to show trucks and accessories. I have been wondering if I should have worked out something for truck count in power query. Your thoughts?

[pranamg]

I agree. It might be simpler to manage in Power Query than in DAX.

[lomorris]

pranamg,
I got it working by applying the following. I took the measure code you gave me and created a new measure that filters the items correctly. Below is a sample of the code and a screenshot.

your measure:
Invoice Item Count =
CALCULATE (
COUNTROWS ( VALUES ( ‘Invoice Transactions’[Serial Number] ) ),
FILTER (
‘NEQ General Ledger Entries Revenue’,
‘NEQ General Ledger Entries Revenue’[General Product Posting Group] = “FOR”
)
)

Additional measure that calls your measure:

Truck Count = CALCULATE([Invoice Item Count],‘Invoice Transactions’[Prod Posting Group] = “FOR”)

Thanks for working with me on this, you help lead me to a final solution.