Filter with an excel doc.

[Jonathan99]

Hi everyone!

I have created a report page where a calcutated a bunch of different things, but that's not important. The thing is I have a huge table with differnet types orders.

I want to be able to sortout some rows for example [FI01|W7] so this row isn't included in my calculations. Basically I want to be able to choose a Company Code and only those document types that I want to calculate will show up.

The thing is I can't change this huge table and just remove the rows I don't want, because it contains every piece of information there is on that specific order.

So I have created another table where I can choose which Document Type that I want to include when I choose a specific Company Code.

Where I choose which Document Type that belongs to that Company Code. Simply by putting a "1" here and there.

I have tried diffrent things but none seems to work they way I want, maybe you guys have a way to solve this? Maybe I just approached this problem form the wrong angel?

Here are the files that I'm working with:

 Dropbox Folder

Thanks in advance!

Jonathan

[LaurentCouartou]

In that case, I would basically add a new column to the main table, so its structure would like this:

- Company Code

- Document Type

- Is relevant 

Where Is relevant would just take the values 0 or 1.

Having a reference table is a good idea, but I would  avoid using one row per document type. Just prefer a simple list:

- Company Code

- Document Type

You could just use the merge operation in the query editor, and then add a custom column to generate Is relevant (preferred method) or do the same thing in DAX.

Those are the general ideas (more details when the samples are available).

[Jonathan99]

Sorry for the delay @LaurentCouartou, but I have now created a Dropbox sharefolder with all the samples.

Hope you can access them and help me with my problem. 🙂

Thanks!

[Floriankx]

Hello, I think the easiest way would be a slicer. So you can slice by company code and document type.

A little bit more complicated would be to work with your Table which links Docoument Types to company codes.

But you can work with it if you unpivot all columns except the first one in Power Query. 

Result would be: Document Type, Company Code, Yes/No (1 or 0). This table you could use in FILTER context.

[Greg_Deckler]

Need, sample/example data that can be copied and pasted along with expected result.  Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Jonathan99]

Oh, sorry

I'm quite new her, didn't realise that samples were so important.

I will add them right away.

Thanks for the tip!