cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter with an excel doc.

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.

Huge table.PNG

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.

Sorting Doc..PNG

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

 

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Filter with an excel doc.

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Filter with an excel doc.

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!

Highlighted
Solution Sage
Solution Sage

Re: Filter with an excel doc.

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.

Highlighted
Solution Supplier
Solution Supplier

Re: Filter with an excel doc.

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

Highlighted
Frequent Visitor

Re: Filter with an excel doc.

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!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors