cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Visual level filter work but not in the measure?

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")
)

 

 

Bill Selected.pngNo Bill Selected.png

 

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"

 

 

Document Type Shown.png

1 ACCEPTED SOLUTION
Super User II
Super User II

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"
)

View solution in original post

6 REPLIES 6
Super User II
Super User II

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"
)

View solution in original post

thanks so much, it worked!

Resident Rockstar
Resident Rockstar

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?

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. 

 

 

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.

 

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")
)
 

 

 

 

table relationships.png

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors