Forum Discussion
Filtering based on main company
Hi, I am building a dashboard but I have run into the following issue and maybe I am overlooking something simple but I have no idea how to do this.
I have a table with my customers and in that table every customer has a number which is the first column on the picture down below. Some customers are part of a bigger organisation and if that is the case you will see the number of that main organisation on the second column.
I am making a dashboard regarding orders and would like to only see the main companies in my slicer filter. When selecting that in my slicer I want it to filter in a way so that it leaves the main company + possible smaller companies that are under it.
So that I can simply see the statistics for that entire company with 1 filter.
In my semantic model, the customer table is linked with a one to many relationship to the orders table, with the customer-ID(first column)
So lets say we have a table dim_Debiteuren, like below,
DebiteurId DebiteurKetenId Naam 1 1 Big Comp 2 1 Big Comp B2B 3 1 Big Comp Consumers 4 4 Small Shop 5 5 Corner Shop We can then add a computed column, like below
KetenNaam = VAR mainId = [DebiteurKetenId] RETURN CALCULATE(FIRSTNONBLANK(dim_Debiteuren[Naam],1),ALL(dim_Debiteuren),dim_Debiteuren[DebiteurId]=mainId)Resulting in this table:
7 Replies
So lets say we have a table dim_Debiteuren, like below,
DebiteurId DebiteurKetenId Naam 1 1 Big Comp 2 1 Big Comp B2B 3 1 Big Comp Consumers 4 4 Small Shop 5 5 Corner Shop We can then add a computed column, like below
KetenNaam = VAR mainId = [DebiteurKetenId] RETURN CALCULATE(FIRSTNONBLANK(dim_Debiteuren[Naam],1),ALL(dim_Debiteuren),dim_Debiteuren[DebiteurId]=mainId)Resulting in this table:
This worked perfectly, thank you both for your help!
and where are stored your data for the main companies?
In another table?
What is the link of this other table and the customer table ?
Alright so on the picture above you can see the following:
- "dim_Debiteuren" is my customer table, in this table you can see one column marked with green, this is the unique company ID which is the first column in the image I send before. The column marked with blue is the ID of the main company and if there is no main company it is the same as the first column.
- "fact_orders" is my orders table and as you can see it is linked to my customer table with the green marked column.
I hope this answers your questions and you can assist me with my problems. The red is for me to cover some information.
Maybe something like that can help you:
Create a calculated column called Type of company with the following logic:
TypeOfCompanies =
IF('dim_Debiteuren'[DebiteurID] = 'dim_Debiteuren'[DebiteurKetlen], "Main Company","Small companies")
Then you create a slicler, you add TypeOfCompanies and just under DebiteurKetlen (I'm not able to read the word correctly, add also the right name in the calculated column)
Then you should have in you slicler
- Main company
- CompanyA
- CompanyB
- CompanyC
- Small companies
- Company Y
- Company Z
Is it what you are trying to achieve?
Not really what I am looking for, sorry if I was unclear. I basically want this a slicer which shows the main companies first and if required you can also drill down to the smaller companies under it. Only I want it to show the company name instead of the number. The company name is just a column in the customer table.
- Main company
So just to clarify, imagine you have the following extract
CompanyID CompanyName Main Organisation 1 Microsoft 1 2 Pomedor 1 3 Adventure 1 4 Thaler 4 5 ERPSoft 5 6 SAP 6 So what you would like to achieve is:
- Microsoft
- Microsoft
- Pomedor
- Adventure
- Thaler
- Thaler
- ErpSoft
- SAP
- Thaler
Am I understanding you need correctly?
- Microsoft
