Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi!
I have this table:
level 0 = is sales product, level 1 = raw material for manufacturing of level 0
If i am filtering for Item no "B", i want to see not only the rows with this item no. but the line on zero level (sales product). Is it possible, please?
I assume you want to do that in DAX? Have you tried using disconnected tables yet?
Not sure I fully understand the ask, but here is what I did:
1. create a calculated table
Slicer = values('Item'[Item No.])
this table is disconnected from the data model, and is used to feed the slicer.
2. Added a measure to calculate if a row should be included.
Include = if(SELECTEDVALUE('Item'[Item No.])=SELECTEDVALUE(Slicer[Item No.]) || SELECTEDVALUE('Fact of sales with raw material'[Level])=0,1,0)
This will tag the rows for the selected item plus all rows for the level 0 (and all rows where the level is missing!)
3. added a visual (or page) filter that filters by Include=1
There is always level value in the source table! This solution duplicates rows (because disconnected table is added??).
Rows with level = 0 should not always be displayed, only if for invoice no. is selected some Item no. on level =1
For example: if i choose C value, i want to show this 2 rows:
If i choose B value, this rows is necessary to show:
... and multiple selection is needer (sorry, that i don´t say it before). Is it possible, please?
For example: if i choose C value, i want to show this 2 rows:
ah, so you want it by invoice number too?
"... and multiple selection is needer (sorry, that i don´t say it before)."
that makes it quite a bit more complex. Show what you want to see when B and C are selected.
Include = if(SELECTEDVALUE('Item'[Item No.]) in Filters(Slicer[Item No.]) || SELECTEDVALUE('Fact of sales with raw material'[Level])=0,1,0)
@lbendlin multiple choice propably OK, but still if Level=0 always the row is included (there is no filter on Invoice No. 😞
Power BI has no idea which invoice you are interested in. You need to add that filter yourself.
@lbendlin And that is the case! I need overview throught all of the invoices.
User story:
I have cca 20 items for which i need detailed overview (consumption of raw material, invoice number and quantity of sales product which is final product of manufacturing - level = 0). For example: i see that for invoice number 123333 i used 5kg of raw material called Beta caroten and for quick check i need to see quantity of sales product (for checking if it is possile produce this quantity from quantity of raw material).
I believe that it must be possible to used your first part
if(SELECTEDVALUE('Item'[Item No.]) in Filters(Slicer[Item No.])
and add second part- something like countx of selected item no. (slicer) in all table for Invoice number from concrete line... if this number is 1 or more than included = 1
Not necessarily in dax. I'm attaching a powerbi file, could you please show me what you mean?
https://drive.google.com/file/d/1n0kcL08uGdjxpjXroiT1Ng1Sr-QGR7bO/view?usp=sharing
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |