Slicer and table that works on same column.

[Ratax]

Hey

I Have the table

Table

"FKProductionItemID" refers to a specific production item

"FKProductionsItemDataTypeID" referes to what DataType is submitted for the each productionItem

"ItemDataValue" refers to the value submitted for the specific production item and the specific Datatype. 

I need to show all FKProductionItemID (table) with the ItemDataValue when the FKproductionItemTypeID is equal to 1.

And i need to filter it (slicer) on the ItemDataValue when the FKprodcutionItemTypeID is equal to 2.

I setup up a slicer with a filter on FKprodcutionItemTypeID = 2 but that filter also works on the table so the columns containing FKproductionItemTypeID is equal to 1 doesn't show up.

[Mariusz]

Hi @Ratax 

If you are looking to retrieve ItemDataValue for FKproductionItemTypeID = "1" while selecting FKproductionItemTypeID  = "2" on the slicer, then you can use a expression like below.

Measure = 
VAR _type = VALUES( 'Table'[FKProductionItemID] )
RETURN 
CALCULATE( 
    SUM( 'Table'[ItemDataValue] ),
    ALL( 'Table' ),
    TREATAS( _type, 'Table'[FKProductionItemID] ),
    'Table'[FKProductionsItemDataTypeID] = "1"
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Ratax 

Can you create a sample of the desired outcome based on the data provided?

Best Regards,
Mariusz

Please feel free to connect with me.

[Ratax]

Slicer

The slicer would looks like this.

I added ItemDataValue to the field in the slicer, and put a visual filter that filters FKProductionsItemData=2. 

The table would look like this. It returns all the FKproductionItemID with ItemDataValues where FKproctionsDataTypeID =1. 

[v-chuncz-msft · Accepted solution]

@Ratax 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Tricky-Slicer-Options/m-p/573381#M270846