Create Dynamic Filter

[tsumit07]

Hello Everyone, 

I am trying to reach a task where i have to created filter on child component column and find all result with parent and all its child.

I have used approach grouping approach (Grouped  Child items with Parent ) and than created new table with Union child parent.

Also once data is filtered , it should color all parents in different colours. for this i have used ( Randx and calculating MOD than conditional colouring )

I am facing issue when i try to filter one of the component (with most data) getting error data visual exceeds its limit.

Could you please suggest any different approach or solution how can i fix this ?

Thank you all in advance.

original table 

BOMComponent BOM1 COM1 BOM1 COM2 BOM1 COM3 BOM2 COM1 BOM2 COM2 BOM2 COM3 BOM3 COM1 BOM3 COM2 BOM3 COM3 BOM3 COM4 BOM4 COM1 BOM4 COM2 BOM5 COM1
Data filtering should look like this when filter Com4 only
BOMComponent BOM3 COM1 BOM3 COM2 BOM3 COM3 BOM3 COM4
Filter Component = Com1 should show all BOM incuding its components
Filter component = Com4  should only show BOM3 and so on
Once filtered Would like to colour BOM in different colour alternativey

[Anonymous]

HI @tsumit07,

Current you can't use default filter effect to achieve these, I'd like to suggest you extract the field value to create a unconnected table as source of filter.
After this, you can write a measure formula to compare table records and return flag. Then you can use for conditional formatting or put it on 'visuals level filter' to filter records.

formula =
VAR selected =
    VALUES ( newTable[Component] )
VAR BomList =
    CALCULATETABLE (
        VALUES ( Table1[BOM] ),
        FILTER ( ALLSELECTED ( Table1 ), [Component] IN selected )
    )
VAR currBom =
    SELECTEDVALUE ( Table1[BOM] )
RETURN
    IF ( currBom IN BomList, "Y", "N" )

BTW, current power bi does not support create dynamic calculated column/table based on filter effect. They not work on the same level and you can't use their child level of effect its parent.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[tsumit07]

Hi Xiaoxin, 

I have followed the steps as suggested below.

Created new unconnected table and used same formula for the measure and used measure in on Visual filter.

Not getting any result back.( Only When i create the relationship between old and new table than i get the result/results)

Please suggest if i am doing something wrong.

Regards 

S