One to Many Relationship Problem

[dzakyramadani]

Im trying to create a table that list all value from table 1(combination of LOB, Division, Dept) and add information based on Table2(Fact table). Ive made a compositekey to bridge the combination of both table so Table1 can act like a dim.

i want Table1 to filter Table2, not the other way around but in reality When i selects Table1 values and then add year from table2, the departments got reduced to what that only exist in table2 that year.

Even though ive tried mutiple time to make Table2 to Table1 one to many relationship, it always revert to many to one.

for an illustration:

Number	Alphabet
1	a
2	b
3	c
4	d
5	e

table1

Number	Alphabet	Color
1	a	red
1	a	red
2	b	yellow
2	b	yellow
3	c	blue

  Table2

Number	Alphabet	Color
1	a	red
2	b	yellow
3	c	blue
4	d
5	e

this is what i want to achieve in a visual. i have some number in my dim that will be used as a weight to calculate something, therefore merging will just create alot of duplicate value for the rows in my fact table.

[cengizhanarslan]

Please try the formula below:

Color (measure) :=
IF (
    ISBLANK ( SELECTEDVALUE ( Table2[Color] ) ),
    "",
    SELECTEDVALUE ( Table2[Color] )
)

[dzakyramadani]

Dept Findings Weight = 
VAR TotalWeightedFindings =
    CALCULATE(
        SUMX('Data Audit',
            SWITCH(
                'Data Audit'[Severity],
                "MAJOR", 3,
                "MINOR", 1,
                0
            )
        ),
        ALLEXCEPT(
            'Data Audit',
            'Data Audit'[LoB],
            'Data Audit'[Sistem Manajemen]
        )
    )
VAR DeptWeightedFindings =
    CALCULATE(
        SUMX(
            'Data Audit',
            SWITCH(
                'Data Audit'[Severity],
                "MAJOR", 3,
                "MINOR", 1,
                0
            )
        )
    )
RETURN
IF(
    ISBLANK(TotalWeightedFindings) || TotalWeightedFindings = 0,
    0,
    DIVIDE(DeptWeightedFindings, TotalWeightedFindings, 0)
)

this is the dax im using to somehow generate the same logic as your code. but still no luck. "Mapping All" is Table1 in this case, and "Data Audit" is the table 2.

[mh2587]

Step 1
Go to Table1 in Data view and verify that each LOB-Division-Dept combination appears only once. If any combination repeats, remove or fix it. Table1 must be unique at this grain.

Step 2
Create the composite key in Table1 and in Table2 using the same columns, same order, and same data type. Check for extra spaces or mismatched text that could cause false differences.

Step 3
Open the model view and create the relationship from Table1’s composite key to Table2’s composite key. Set it as one-to-many with single direction from Table1 to Table2. If Power BI changes it back, return to Step 1 and fix duplicates in Table1.

Step 4
Create a measure in Table2 to return the value you want to show, such as color or any numeric value. Do not plan to use Table2 columns directly in visuals.

Step 5
Build the table visual using only columns from Table1 for rows. Add only the measure from Step 4 as the value.

Step 6
Add the Year slicer from Table2. Select different years and confirm that all rows from Table1 stay visible and only the measure values change or become blank.

Step 7
Confirm that no columns from Table2 are used anywhere in the visual, including tooltips or filters, because this will remove rows that do not exist in the fact table.

Step 8
If rows still disappear or the relationship flips again, recheck Table1 for duplicates and incorrect composite key values, then repeat the steps from the beginning.

[dzakyramadani]

no luck, ive ensured all the text are the same and even check with merge. all compositekey of the fact table have matches. but power bi still wont let me one to many. no duplicates no false differences. the same with my illustration of simple data i gave, it just dont work.

[mh2587]

Kindly review both the upper and lower records. I encountered a similar issue previously, which was caused by the same record appearing in both places.

[dzakyramadani]

sorry, what do you mean by upper and lower record? i ensure all the composite key have no duplicate anywhere, all composite key on fact table exist in the table1, and even ensure all the building block of the compositekey type, and value is the same.