Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
| ||||||||||||||||||||||||||||||||
Data filtering should look like this when filter Com4 only | ||||||||||||||||||||||||||||||||
BOMComponent
| ||||||||||||||||||||||||||||||||
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 | ||||||||||||||||||||||||||||||||
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
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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |