Visual level filter work but not in the measure?

[BiUser1123]

Hi , I am trying to calculate the last purchase price for each item. The prices are calculated through many document Types so i want to select only the document type "Bill". As it is shown, the last purchase price (Cost) is blank. But if i add the Document Type in the Visual level filter and set to "Bill", it works perfectly. I am guessing it has to do with how i filter in the measure?

Last Purchase Price =

CALCULATE (

SUMX('INVENTORY VALUES',DIVIDE('INVENTORY VALUES'[Line Inventory Valuation Amount],'INVENTORY VALUES'[Line Inventory Quantity])),

LASTDATE('INVENTORY VALUES'[Related Date]),FILTER('Document Attributes', 'Document Attributes'[Document Type] = "Bill")

)

If I add the document type column, i see that the other document types are empty so it is filtering by Bill somehow. For the Inventory Measure, it is explicitly filtering any document Type but "Bill"

[jdbuchanan71 · Accepted solution]

Your LASTDATE('INVENTORY VALUES'[Related Date]) has no concept of [Document Type] = "Bill" so it is feeding in dates that are not billing dates. Then, when the SUMX calcs it gets a [Related Date] that is not a billing date and a [Billing Type] that is billing. No lines match those conditions so it returns a null. When you apply the filter on [Billing Type] the LASTDATE('INVENTORY VALUES'[Related Date]) line receives that filter so the last date is a billing date.

Try this instead.

Last Purchase Price = 
VAR LastBillDate = CALCULATE(LASTDATE('INVENTORY VALUES'[Related Date]),'Document Attributes'[Document Type] = "Bill")
RETURN
CALCULATE (
    SUMX(
        'INVENTORY VALUES',
        DIVIDE('INVENTORY VALUES'[Line Inventory Valuation Amount],'INVENTORY VALUES'[Line Inventory Quantity])
    ),
    'Inventory Values'[Related Date] = LastBillDate,
    'Document Attributes'[Document Type] = "Bill"
)

[BiUser1123]

thanks so much, it worked!

[Cmcmahan]

If you want a quick and easy method, click on your visualization and set a Visual Level Filter.  You can even leave the FILTER('Document Attributes', 'Document Attributes'[Document Type] = "Bill") portion out of your query at this point.

There's something weird happening with your table relationships.  Can you post a picture of them?  Do Documents with Document Type of bill not have Inventory numbers?

[BiUser1123]

Documents with Document Type bill do have inventory numbers it is just I specifically excluded those numbers through my measure "Inventory". my inventory numbers was double counting the quantity on hand so i had to exclude documents of type bill. 

The issue though is regarding to the measure "Last purchase price". I want to specifically only use document type = "Bill" but that filter does not seem to work. 

FILTER('Document Attributes', 'Document Attributes'[Document Type] = "Bill") does not provide results if i leave the visual level filter set to only use Bill. pretty stuck on this and I am not sure how to proceed. 

[Cmcmahan]

Yes, so can you show us a picture of your table relationships?  You may need to use RELATED in order to get the link to work properly.

What might be happening is you're filtering by Bill Description, but PBI doesn't know how to link a bill description to an inventory entry without you explicitly calling it out.

[BiUser1123]

Here is the table relationships. They are linked by by "LinkToTxnID" .

I updated the measure with Related for the column but it does complain that it cannt find the column document type.

Last Purchase Price =

CALCULATE (

SUMX('INVENTORY VALUES',DIVIDE('INVENTORY VALUES'[Line Inventory Valuation Amount],'INVENTORY VALUES'[Line Inventory Quantity])),

LASTDATE('INVENTORY VALUES'[Related Date]),FILTER('Document Attributes', RELATED('Document Attributes'[Document Type]) = "Bill")

)