Using separate filters for same value on a visual

[artlemaks]

Hi.

I am trying to create a line chart that would compare values of current stock vs stock last time. To compare the values, I am using a column from a database called stocktake_id. What I am wondering is, is it possible to specify a filter for a value, rather than for a whole visual? 

And if not, what's the best way to go about this? I pretty much need to say that value 1 on the trend graph needs to use stocktake_id of 1234 and secondary value needs to use a stocktake_id of 1233. 

Is this something that can be done using filters, or do I need to use DAX to achieve that? If I need DAX, how to I always make sure that the right stocktake_id gets selected, as it needs to be previous ID for that specific site.

I hope the above makes sense, but here is an image showing the fields that I am using right now. 

ID is the id of a store and stocktake_id is the id of an audit from which I am taking the values. What I need to achieve is get Stock at Cost in Secondary Values, to show values for previous stocktake_id for that store ID, which in this case is 7995, so as you can see, it doesn't just decrement by 1.

Thank you for any assistance. 

[v-jayw-msft]

Hi @artlemaks ,

If i understand you correctly, you can't use filters to get the result you want. Table will be filtered by the filter and we can not catch the filter value in DAX formula. I'd like to suggest you to use Slicers.

Best Regards,

Jay

[amitchandak]

@artlemaks , you can use disconnected tables and filter in measure. You can use one disconnected and one connected too .

Refer example of how this is done date. You can do with stock

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[artlemaks]

@amitchandak I don't think this will solve my problem as I'm actually not using any slicers on the visual and want to avoid it. I want the data to be calculated programatically. 

I can easily set the main filter when embedding the report using JS and I have just noticed that I can use a filtered measure to get a value for the 2nd stocktake_id by doing 

Stock at Cost for 7995 2 =

CALCULATE(

    SUM('godigitally_easycoun_st view_stocktake_details'[Stock at Cost]),

    'godigitally_easycoun_st view_stocktake_details'[stocktake_id]

        IN { 7995 }

)

Now, is there a way where I can change that '7995' and instead of having a hard coded value, for the measure to be able to select 2nd highest stocktake_id for the filtered store_id? 

In MySQL it would be something like IN { SELECT MAX(stocktake_id) FROM view_stocktake_details WHERE stocktake_id < (SELECT MAX stocktake_id FROM view_stocktake_details) } but how do I translate that to DAX, if that is even possible? 

[amitchandak]

@artlemaks , that value can come from slicer connected/ Slicer disconnected and this a number you can give from what if parameter

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[artlemaks]

@amitchandak but I don't want to be using slicers. I only want to use a visual filter which I will set to a certain value in JS and I want the DAX to be calculated based on that value. 

[amitchandak]

@artlemaks , see if this can help. https://docs.microsoft.com/en-us/power-bi/service-url-filters

[artlemaks]

@amitchandak I know I can do that and I know that I can set visual filters either through URL or JS when using Power BI embedded, but that doesn't help me with my DAX and selecting the previous stocktake_id based on the filter.