Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Link together a column that is the same in all tables. Like an Enum.



I have a column, division, that is shared between all my tables (shared in a sense that it’s present in all tables and has the same set of values).


Let’s say I have a graph that’s count of placements by division.

Filter off SS.png

What I wish to be able to do is click the bar in that graph and filter the whole table by that division. Currently when I select the graph I only filter the graphs that share the same table.

Filter on SS.png

Clearly this is due to them being dissociated with eachother, is there a way to fix that?


Many Thanks,

Helper I
Helper I

Which formula you are using? DAX or Power Query?


On DAX we are covered as we are using DISTINCT so we shouldn't capture BLANK values od there are aby. However, in Power Query we could have Blanks. Please let me know which one you are using.

Frequent Visitor

Hi Everyone,


I do apologise for the lack of presence, I have been working cilently behind scenes with @DemoFour.


I have used the formula @PawelWrona has given me to generate the division table.

Please see below the current state of the relationships in my data model:


The issue is with the divisions table, as I am unable to relate it to anything (Unless I do it using many to many).

The model that @DemoFour has shared with me was very help however, I am unable to replicate it with my live data.


I'd appreciate any and all help from you all.


Many Thanks,

Responsive Resident
Responsive Resident



If you supply fake data then that will help with understanding your tables and what are your Facts and what are your dimensions. 

I have built similar models to what you are undertaking and I think you could solve your issues with a better data model. 

Hi @DemoFour,


Thank you for your reply.

There are a lot of columns for each table, and it would take a long while to redact all the data.

Therefore I have summarised some data, keeping only important columns:

Client Table

68cd268wbk5o9e3gGeneric CompanyConstruction
73uttpa22hu7iz6kImportant CompanyEngineering & Manufacturing


Contact Table

64m5gs74n5amghmcJohn SmithEngineering & Manufacturing
68s9i5bmo6y4ws2eSteven EvansEnvironmental


Candidate Table

4hhs8ps4v2e4o87eAnthony SmithConstruction
4wo6u7i8impwtjkrAnnie RobertsConstruction


Vacancy Table

IDNameDivisionContact ID
ym56r22ru5kmeu5kJoinerEngineering & Manufacturing64m5gs74n5amghmc
35ek8fqvf6dw6j9nJoinerEngineering & Manufacturing64m5gs74n5amghmc


Placement Table

IDNameDivisionClient IDContact IDCandidate IDVacancy ID
edpzp4b486v44cy7JoinerEngineering & Manufacturing73uttpa22hu7iz6k64m5gs74n5amghmcq582e36u38riqz6hym56r22ru5kmeu5k
xq6u976hxqfer949JoinerEngineering & Manufacturing6cc7nz4uc4i7ftf964m5gs74n5amghmcq582e36u38riqz6hn3isc94u9zo3pt9f


As I said there is no table for Divisions.


I hope the above is of use.


Many Thanks,

Responsive Resident
Responsive Resident


I will have a look, it is good practice to change your keys to intergers to store the model more effectivley and also remove all unwanted columns from the tables. 

Thank you for sharing. 

Hi @DemoFour,


Thank you for the advice, I will keep that in mind in future correspondance.


Many Thanks,

Responsive Resident
Responsive Resident


I have sent PM to you in regards to a model file, as I cant upload them here due to work restrictions. 

I have modeled the data into a Star Scheme and changed the Fact tables to hold the keys to the dimensions. 

You may want to review these links: 

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

You said this is source data from your CRM, this will help you to build the tables you need further up the stack, or if you have a DBA CRM developer, you could work to create views or tables by querying into the source. 

 Best practices for creating a dimensional model using dataflows - Power Query | Microsoft Learn 

Get and transform data with Power BI - Training | Microsoft Learn


Model data with Power BI - Training | Microsoft Learn

I hope that helps, and if you want the file just let me know in PM. 

Kind regards, 


You may want to review this

Helper I
Helper I

Ok, now I think I get what you need.

Starting with the relationship, I can't see clearly your first screenshot on the top, but if I see correctly, you can't create relationship between Division and Placement tables, as you already have relationships between Client and Placement tables. This is easy scenario for Power BI to recognize as introducing ambiguity in your model, making this relationship as inactive.


Whatever is the column used to create relationships between Client and Placement tables, you could make dimension table out of it, and link the tables through dimension table, as you try to do with Division table.


Now, to have one Division table containing all the values from other tables - you can start in Power Query already and use formula:

List.Distinct(List.Combine({Table1[Division], Table2[Division], etc.}))

and then turn this list into table and you have it.


Similarily you can do the same in DAX:

Table =
VAR table1 = SELECTCOLUMNS(Client, "Division", Client[Division])
VAR table2 = SELECTCOLUMNS(Placement, "Division", Placement[Division])
VAR combinedTable = UNION(table1, table2)

RETURN DISTINCT(combinedTable)
Helper I
Helper I

I don't think I understand properly why you need to get distinct values of divisions in each table. Maybe the problem lies closer to the root so in data model. If you could share the bpix file and explain on example what you would like to achievie, then it would be easier to assist you.

Hi Pawel,


Unfortunately, I cannot give you the bpix files as it as real data from our CRM.


The reason I need to get the distinct values is because:


I do not have a table that contains all values of division. Therefore, I need to create one and since I wish to make it future proof the best way I've found to do that would be to get all distinct values from one table.

This does have the issue of possibly breaking if a new division is made in another table.


However, this is not as essential as making the master table for divisions work.

As I said earlier, I cannot seem to link all tables' divisions to the master division table.

See below:

Master table SS.png


Many Thanks,

Frequent Visitor



Thank you very much for all the replies.


From what you said I wanted to try and make a table for the divisions (like an enum) and relate this to the rest of the tables.

However, upon creation I can only relate it to a single table.

Additionally, I need to figure out how to get all unique divisions from the tables. I can do this by just getting all distinct division values from a table but, if there is a division in another table that is not used in the one I have chosen then there will be a discrepancy.

Could you please advise on this?


Many Thanks,

Helper I
Helper I

If for any reason you can't create proper relationships between tables, you might actually have one list of divisions, it can be disconnected from all other tables and use TREATAS function inside your measures, that you use in visuals.

Bur of course it would be better with proper relationships.

Memorable Member
Memorable Member

Hi @CJSGreg,


I think you need to pull the dimention that you want to filter on from the master table (with the division) which is linked to all your tables rather than using the dimention from the "data" table.




Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.