How to filter according to levels

[Hnatecka]

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?

[lbendlin]

I assume you want to do that in DAX?  Have you tried using disconnected tables yet?

[Hnatecka]

@lbendlin Hi, do you think that you could help me, please?

[lbendlin]

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

[Hnatecka]

@lbendlin 

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?

[Hnatecka]

For example: if i choose C value, i want to show this  2 rows:

[lbendlin]

ah, so you want it by invoice number too?

[Hnatecka]

@lbendlinyes, because that's the only way I can see relevant results 🙂

[lbendlin]

"... 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.

[Hnatecka]

@lbendlin 

[lbendlin]

Include = if(SELECTEDVALUE('Item'[Item No.]) in Filters(Slicer[Item No.]) || SELECTEDVALUE('Fact of sales with raw material'[Level])=0,1,0)

[Hnatecka]

@lbendlin multiple choice propably OK, but still if Level=0 always the row is included (there is no filter on Invoice No. 😞

[lbendlin]

Power BI has no idea which invoice you are interested in. You need to add that filter yourself.

[Hnatecka]

@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

[Hnatecka]

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