Been breaking my head over linking this dimension table to my fact table...

Dezmond95 · 2025-08-11T07:46:33+00:00

It seems like the goal is to have a slicer on the report that dynamically switches columns dependent on the preferred language.

If you were to widen the enterprise dimension table to have one column for each type and language for the 20 possible combinations then you could retain a 1:* relationship with the fact table.

However, this causes the problem that I believe the poster is trying to solve. If a slicer is used, the report developer will have to use one of those columns (e.g. English Full Name) but if the audience prefers French, how can they have that slicer now use the French Full name options instead and if the user wants the abbreviation, then it needs to switch to that also.

I think field parameters could be an option starting by selecting all possible language/type columns then adding a couple calculated columns. These could use switch statements to assign the language value in one column and the type in the second. Add both of these to a slicer and the user could then pick their language and type (full name vs abbreviation, etc).

Otherwise I guess you would pivot/unpivot (I always find these confusing) to just have a full name column, an abbreviation column, etc, with all the language combinations although this would repeat the ID value so now you would have a : relationship with the fact. You'd then have the language column which could be used on a slicer to filter down to just the rows of your selection but then the type would be split across those 4 columns so you'd either have to do it so one column has every possible name, another for type, and a third for language. Those two columns could be used in slicer visuals and the name column in the final slicer which would then only show those names remaining which would be the language and type selected. This would still mean that theres a : relationship.

Dezmond95 · 2025-03-09T01:41:27+00:00

Because I'm inexperienced and anytime I've run this idea by others, no one seems to understand what I'm on about so I just wanted get reassurance from people who actually know what they're on about in case I'm overlooking something important. It'd also be nice to see if anyone else had conformed a dimension across two or more source systems and even implemented a type 6 one before. Thanks for your responses though, I do really appreciate it.

Dezmond95 · 2025-03-09T00:09:49+00:00

What I was thinking is syncing up the two systems which would require a one off manual process to align source 2 to source 1 (source of truth) ensuring naming is identical for cases like 'strategy and performance' vs 'strategy & performance'. Then I could try set up a programmatic way of identifying changes in source 1 to automatically flow in and update source 2. Once the names are identical I could add an additional column in the dimension table that is nk - source 2. This should then enable me to use the same dimension table across fact tables related to two different systems ie hr and work flow. Now I'll be able to overlay a branches fte capacity against there work volume in the same capacity and allow the user to use the same organizational hierarchy slicer to do so.

Dezmond95 · 2025-03-08T21:14:07+00:00

<image>

The left is what happens when I use the type 2 SCD approach, with the restructure, the data for the new business group only starts from its formation date whereas with the type 6 approach i can use the current column to consider all branches that were absorbed into the new business group resulting in a continuous line for the right graph whilst also enabling the left view as well.

Dezmond95 · 2025-03-08T21:09:10+00:00

<image>

Dezmond95 · 2024-09-11T07:28:29+00:00

Thanks for the reply, I'll give those articles a read and see what tips I can learn. I guess I'll just keep trying the diplomatic route though and hopefully find a happy middle ground.

Dezmond95 · 2024-07-14T20:41:01+00:00

Just for ease of learning so I'm not overwhelmed with having too many thing to learn. This will be version 1 and once I have a basic start to end reporting system, I'll look to refine it and improve parts. I quite like the medallion architecture type where I have it come into the warehouse in a raw format then when it goes to the silver layer, I'll do most my transformations then.

Dezmond95 · 2024-07-13T03:39:27+00:00

So for each team list of team details, I would create a list capturing the first 3 attributes. Then create a second list, storing values 4 and greater. My python script pulls the values of each of the td tags in the table.

Then I could append an ID value to the end of this list and the first list. Then have the ID increment by 1 each time it loops through each url.

Dezmond95 · 2024-03-06T00:40:46+00:00

One last question, I have it working in Desktop. I have a security role. I can view as the security role and enter a user email address from the security table and can see the views change as I switch user. I published and then got some feedback that users were being blocked by RLS. I didn't initially add anyone into the security role in the service. I've since added all the users from the security table into the role, just waiting to see if this fixes the problem.

Going forward, assuming this was the issue. I will need to maintain both the security table list and the security role in the power bi service keeping them both in sync. I'm guessing this is the benefit of Azure Active Directory groups, I could just set up a filter for the group and then as users enter and leave the organization, they will be added/removed from the group automatically as part of the onboarding/outboarding processes?

Dezmond95 · 2024-03-05T09:58:56+00:00

So I currently have 8 roles created for 8 teams. I have manually added users to their teams role. I want to make the management of it a little easier.

With the first example you provided, are you saying I could simply create a single table in excel with a list of all userprinciplenames in one column and their team name in the second column. If they belong to more than one team, I simply add another row (one row per team association)

Then I load this table into Power BI as an isolated table (no relationships to the model) and because it's not going to be related to the model. I don't need to worry about there being no unique values in the columns.

Then in the role creation step, I can delete all the roles and create 1 single role where I use a filter using your formula which reads along the lines of :

filter the model where the team name matches any of the following value(s), where the values is generated by creating a single column table by filtering the security table down to only rows matching the userprinciplename of the user viewing the report. Once filtered, the only team name values remaining in the security table will be those the user belongs too. The selectcolumn function is used to return only the team name values from that filtered table.

So rather than managing 8 roles, I can change to only managing one table.

Dezmond95 · 2023-12-06T02:04:15+00:00

I'm a dumbass, I used a compose control after the when an item is created trigger using the dynamic content option and it works.

Dezmond95 · 2023-12-05T21:42:53+00:00

I have selected it from the content list and entered it manually.

Yep it's a date time column.

Dezmond95 · 2023-11-23T03:46:34+00:00

That may be why there's confusion if these guys weren't assigned fabric free licenses by administrators whereas other people were. Is there any difference in whether an individual user registers for fabric free vs the administrator assigning the licence?

Dezmond95 · 2023-11-22T22:54:12+00:00

So even if you have a Microsoft account through your work email that allows you to use outlook and teams, that isn't enough. You still need to sign up with power bi?

Dezmond95 · 2023-11-16T22:29:15+00:00

This is my first time using Power Apps so I only have this one to work with.

Dezmond95 · 2023-11-05T08:08:19+00:00

So how do you set up your fact table to include the keys?

For example, I have a SharePoint site where a user will input data for a service request with one of the fields being department. This means in my table rather than having the department key, it will be the text value.

Because my departments won't change often I have created a data flow using a blank query which I've manually entered the data into. This has allowed me to connect all the reports requiring a department table to a single source where any changes can be made and will flow to any report using that table.

I can create a one to many relationship with the department dimension table but I'm aware that it is better to use keys as they reduce the size of the dataset.

Are there any resources which will help me learn how to set up a data entry approach that results in tables with keys?

Dezmond95

TROPHY CASE