Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have "flattened" my bom structure to one table with columns like this:
I have product table (like item data) for attribute values (like std price) by part:
Now:
I am able to create a hierarchy from partid_level columns - and it is working: when I select mainitem I am able to see the parts under it in matrix visual:
What I need now:
1. way to show qty for each part based on the qty_level columns
2. std price value from my item table for each part
3. measure to calculate qty_level x std_price
Anyone?
Solved! Go to Solution.
@TTS , For That you can have measure like
Sumx(Table, Table[Qty] * Table[Price])
if price is in master(1-m Join)
Sumx(Table, Table[Qty]* realted(Item[Price]) )
Because you have different levels, Use isinscope to switch such levels, Lowest level first
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
Hi users, @amitchandak , @TTS
Great that you got required output.
But I want to know how do modelled the tables there because when you join mainitem (BOM table) with PART (DIM_PARTS), you will get the same price of mainitems for part levels of that mainitem.
Expectation is to get price of part level items in the matrix.
@TTS , For That you can have measure like
Sumx(Table, Table[Qty] * Table[Price])
if price is in master(1-m Join)
Sumx(Table, Table[Qty]* realted(Item[Price]) )
Because you have different levels, Use isinscope to switch such levels, Lowest level first
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
Hi @amitchandak
And great thanks for Your message; I was too close to see it clearly from my data model point of view.
I made few changes and was able to avoid sumx by using hierarchy values and by creating enough measures...and then switch ( true () ... to get correct measure to correct level.
Logic works...now it is time to check values.
Great thanks! 💪
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |