Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jonathan99
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
LaurentCouartou
Solution Supplier
Solution Supplier

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

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
Solution Sage
Solution Sage

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
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.