Forum Discussion

dzakyramadani's avatar
dzakyramadani
Regular Visitor
7 months ago
Solved

One to Many Relationship Problem

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 ...
Need Help
  • v-achippa's avatar
    v-achippa
    6 months ago

    Hi dzakyramadani,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you cengizhanarslan and mh2587 for the prompt response. 

     

    Based on your description this is expected behavior. Even with a correct one to many relationship, power bi visuals will only show dimension rows that have matching fact rows when any column from the fact table is used in the visual or slicer.
    Relationship direction controls only filter propagation. To keep all dimension rows visible, use only Table1 columns in rows and return fact data only through measures.

     

    Thanks and regards,

    Anjan Kumar Chippa

cengizhanarslan's avatar
cengizhanarslan
Super User
7 months ago

Please try the formula below:

Color (measure) :=
IF (
    ISBLANK ( SELECTEDVALUE ( Table2[Color] ) ),
    "",
    SELECTEDVALUE ( Table2[Color] )
)
  • dzakyramadani's avatar
    dzakyramadani
    Regular Visitor
    6 months ago
    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.