How can I use a measure as a filter (is it possible) and also drill down

[Anonymous]

I have a measure that shows the total number of items that are out on a PO but not invoiced yet, this obviously filters down when a supplier is selected.

That works fine in it's simplest form, but I was hoping to use it for two purposes.

Is there a way that when I click on it or interact with it in some form or fashion the table I have the report page is then limited to only show the not invoiced PO, so it filters the other visualisations or allows me to drill through the measure to then see the data behind it that shows all the not invoiced PO items?

Is that possible?

[TomMartens]

Hey @Anonymous ,

this is not possible, at least not the way you describe your requirement.

This is basically because a measure is not related to an object that allows filtering another table.

For this reason you might create a different measure that instead
counting the number of products checks if 
a Product is inside the table containing the products that have not been invoiced yet

Then you use this measure to filter down the products in your visuals, which then will show only the products that have not been invoiced.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

[Anonymous]

Thanks for your reply.  This is the measure I have now.

QTY Not Invoiced = CALCULATE(SUM(HC_purchasing[Quantity]), 'HC_purchasing'[Invoice Status] IN {"Not Invoiced"})

Are you saying I should create a new table that will only contain the Not Invoiced items and then base the measure on that new table?   
Not sure how that would be used as a filter though, then again I am rubbish at measures outside of very basic ones and not even sure I am on the right track here.

[TomMartens]

Hey @Anonymous ,

here you will find a very little example that demonstrates what I was talking about: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EagF-5dX4cpKrnec4O34pz0BU8wV-hcC0KE1FDne_9NF1g?e=4OWE1g

Hopefully, this gives you an idea how to tackle your challenge.

The measure

Products not sold = 
var __ProductsSold = VALUES( 'Sales Fact'[Product] )
return
COUNTX(
    VALUES('Product Dimension'[Product] )
    , IF( NOT('Product Dimension'[Product] in __ProductsSold )
        , 1
        , BLANK()
    )
) 

This measure is used to "filter" the products that have not been sold in the 2nd table.

Regards,

Tom

[Anonymous]

Thanks I will have a look at that and see how to adapt it to what I need for my attempt.

Thanks.