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

[CJSGreg]

Hi,

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.

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.

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

Many Thanks,

[PawelWrona]

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.

[CJSGreg]

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,

[DemoFour]

@CJSGreg 

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. 

[CJSGreg]

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

ID	Name	Division
68cd268wbk5o9e3g	Generic Company	Construction
73uttpa22hu7iz6k	Important Company	Engineering & Manufacturing

Contact Table

ID	Name	Division
64m5gs74n5amghmc	John Smith	Engineering & Manufacturing
68s9i5bmo6y4ws2e	Steven Evans	Environmental

Candidate Table

ID	Name	Division
4hhs8ps4v2e4o87e	Anthony Smith	Construction
4wo6u7i8impwtjkr	Annie Roberts	Construction

Vacancy Table

ID	Name	Division	Contact ID
ym56r22ru5kmeu5k	Joiner	Engineering & Manufacturing	64m5gs74n5amghmc
35ek8fqvf6dw6j9n	Joiner	Engineering & Manufacturing	64m5gs74n5amghmc

Placement Table

ID	Name	Division	Client ID	Contact ID	Candidate ID	Vacancy ID
edpzp4b486v44cy7	Joiner	Engineering & Manufacturing	73uttpa22hu7iz6k	64m5gs74n5amghmc	q582e36u38riqz6h	ym56r22ru5kmeu5k
xq6u976hxqfer949	Joiner	Engineering & Manufacturing	6cc7nz4uc4i7ftf9	64m5gs74n5amghmc	q582e36u38riqz6h	n3isc94u9zo3pt9f

As I said there is no table for Divisions.

I hope the above is of use.

Many Thanks,

[DemoFour]

@CJSGreg 

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. 

[CJSGreg]

Hi @DemoFour,

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

Many Thanks,

[DemoFour]

@CJSGreg 

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, 

Russell

You may want to review this

[PawelWrona]

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)

[PawelWrona]

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.

[CJSGreg]

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:

Many Thanks,

[CJSGreg]

Hi,

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,

[PawelWrona]

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.

[jbwtp]

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.

Cheers,

John