topic How to filter according to levels in DAX Commands and Tips

How to filter according to levels

Hnatecka — Wed, 30 Sep 2020 12:58:47 GMT

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?

Re: How to filter according to levels

lbendlin — Sun, 04 Oct 2020 22:23:32 GMT

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

Re: How to filter according to levels

Hnatecka — Mon, 05 Oct 2020 05:42:42 GMT

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

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 07:26:38 GMT

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

Re: How to filter according to levels

lbendlin — Fri, 16 Oct 2020 13:28:00 GMT

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

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 16:23:35 GMT

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?

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 16:24:37 GMT

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

Re: How to filter according to levels

lbendlin — Fri, 16 Oct 2020 16:37:24 GMT

ah, so you want it by invoice number too?

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 16:40:49 GMT

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

Re: How to filter according to levels

lbendlin — Fri, 16 Oct 2020 17:46:31 GMT

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

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 17:53:39 GMT

lbendlin

Re: How to filter according to levels

lbendlin — Fri, 16 Oct 2020 18:07:03 GMT

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

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 18:38:35 GMT

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

Re: How to filter according to levels

lbendlin — Fri, 16 Oct 2020 19:30:57 GMT

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

Re: How to filter according to levels

Hnatecka — Fri, 16 Oct 2020 21:29:38 GMT

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