Wanting to combine ISFILTERED function with an calculation for an specific value

[Raaverok]

Hi all,

So I am in need of some help with my Power BI calculcations. Short recap below :

We have different Bill of Materials in our ERP system and I want to calculate what happens when 1 item has been delayed or will be purchased later. The goal is to optimize the green period, as seen in the below screenshot.

What I have done is the following :

- I have build an report showing the different stages (production, transport etc) for all the items;

- I have made 1 overview showing the BOM item with the leadtimes combined.

What I now need is an measure in which I can calculate what happens with 1 or more items when there is an change in the different leadtimes.

I have already made an What-IF function named WeekDifference. The calculation that I have is the following:

Internal Leadtime = 1 + WeekDifference

So I want to change the WeekDifference based on an slicer and using the What-If function.

The tricky part is that I want to have the WeekDifference amount only added to the items which I have filtered.

I have tried the ISFILTERED function, but if I do so all the items will get the WeeksDifference added to the internal leadtime as the Filter value = TRUE for all the items.

So I want only the filtered items to get the WeeksDifference added and I want to have all the items into 1 visual as the screenshot below has stated.

Slicer set to 7 and 1 item filtered, first blue part shows 8 now for all the items instead of the 1 filtered :

Much appreciated if you guys can help me!

[amitchandak]

@Raaverok , Not very clear. But isfiltered also work for a row, if you use the same column in visual,

You can try like calculate(isfiltered (Table[Week]), allselected()) , this will give overall (means will only be true when slicer is filtered)

[Raaverok]

Thanks for the feedback, this unfortunately did not work.

What I want to archieve is the following:

If I filter an item I want for only that item to have the selected leadtime added to the calculation. For all the other items the selected leadtime does not have to be added to the internal leadtime.

This works if I filter all the items so I only get the item which has been selected in the slicer. But I want to keep all the items of the BOM in the overview and only for the selected item I want to have the leadtime added to the internal leadtime. 

[v-chenwuz-msft]

Hi @Raaverok ,

I have a method that maybe you can try.
Step 1, create an item table via values(table[item])
Step 2, create a one-way one-to-many relationship between the item table and the fact table.
Step 3, the bar chart uses the fields from the item table

Step 4, create a mesure use the following code:

Measure = 
var _1 = CALCULATE(SUM('Table'[values]),ALL('Table'[item]))
var _2 = SUM('Table'[values])+SELECTEDVALUE('what-if'[what-if])
return
IF(SELECTEDVALUE('Table'[type])="time1"&&ISFILTERED('Table'[item]),_2,_1)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raaverok]

Hi v-chenwuz-msft,

This helps a lot and I am almost there!

The only thing I do not understand is the "Type" value. How can I add this column to my existing file? 
It looks like you made an time 1 for the 1st time the item is visible in the table, tim2 for the 2nd time etc.

How can I add this to my existing PBI? After this has been done I think that that is the solution.

Thanks in advance!

[v-chenwuz-msft]

Hi @Raaverok ,

The "type" is the same as your field where has internal leadtime , production time, shipping time etc. If these legend is not column than measure. You should change all the measures which does not need to add what-if as 

CALCULATE([your measure] ,ALL('Table'[item]))

If you need more help to modify your pbix, please share your pbix with fake data.

Best Regards

Community Support Team _ chenwu zhu

[Raaverok]

Hi @v-chenwuz-msft ,

I have added an example file which is based on an Excel file with the same measures, hopefully you can see what needs to be done in the measure.

The measure has been added in the "Componenten JET" tab.

Thanks in advance for your help, much appreciated! 

Power BI question - PBI file 

[v-chenwuz-msft · Accepted solution]

Hi @Raaverok ,

In your existing pbix file, please follow these steps to do that.

1 create a measure named M_filter_item_table to filter the item number in the slicer. And put this measure in the filter pane on this slicer and set it show items which is 1.
M_filter_item_table = IF( SELECTEDVALUE(ItemTable[Item Number]) in VALUES('Componenten JET'[Item No.]),1)

2 change the measure to the following code and replace internal leadtime.

Measure =
VAR _1 =
    SUM ( 'Componenten JET'[Internal Leadtime] )
VAR _2 =
    SUM ( 'Componenten JET'[Internal Leadtime] )
        + SELECTEDVALUE ( 'what-if'[what-if] )
RETURN
    IF (
        SELECTEDVALUE ( 'Componenten JET'[Item No.] )
            IN VALUES ( ItemTable[Item Number] ) && ISFILTERED ( ItemTable[Item Number] ),
        _2,
        _1
    )

Pbix in the end you can refer.
Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Raaverok]

Many thanks! This works like a charm 🙂