Seeking Solutions for Data Model Issues

[Siboska]

I have a data model that I'm struggling to solve, and I'm unsure if a solution exists.

The data model is structured as follows:

The "Project" table is central and filters the "Risk" table in a 1-to-many relationship. There is also a relationship between the "Project" and "Action" tables, which is also 1-to-many.

The "Risk" table is connected to the "Project" table as previously mentioned. Additionally, there's an inactive 1-to-many relationship from the "Risk" to the "Actions" table.

The "Action" table contains rows that can have either a Project ID, a Risk ID, or both.

I have two slicers. One filters based on the "Project" columns, and the other filters based on the "Risk" columns. I want the flexibility to use both slicers individually or simultaneously.

Desired Output: My goal is straightforward: I want a table based on the "Action" table displaying all action names without any additional calculations.

My initial approach was to create a measure that could serve as a filter for the table visual, as illustrated below:

RiskFilterTest = 
VAR CurrentActionRisk = MAX('TableActions'[pmi_Risk])
VAR SelectedRisks = ALLSELECTED('Project risks')
RETURN
    IF(
        NOT ISEMPTY(SelectedRisks),
        CALCULATE(
            IF(
                CurrentActionRisk IN VALUES('Project risks'[pum_RiskId]),
                1,
                0
            ),
            USERELATIONSHIP('TableActions'[pmi_Risk], 'Project risks'[pum_RiskId])
        ),
        1
    )

ProjectFilterTest = 
VAR CurrentActionProject = MAX('TableActions'[ProjectId])
VAR SelectedProject = ALLSELECTED('Project')
RETURN
    IF(
        NOT ISEMPTY(SelectedProject),
        IF(CurrentActionProject IN VALUES('Project'[ProjectId]), 1, 0),
        1
    )

CombinedFilterTableMethod = 
VAR RiskTable = 
    FILTER(
        'TableActions',
        [RiskFilterTest] = 1
    )

VAR ProjectTable = 
    FILTER(
        'TableActions',
        [ProjectFilterTest] = 1
    )

VAR CombinedTable = 
    UNION(
        SELECTCOLUMNS(RiskTable, "ActionId", 'TableActions'[pmi_ActionId]),
        SELECTCOLUMNS(ProjectTable, "ActionId", 'TableActions'[pmi_ActionId])
    )

VAR CurrentActionId = MAX('TableActions'[pmi_ActionId])

RETURN 
    IF(CONTAINS(CombinedTable, [ActionId], CurrentActionId), 1, 0)

And then I apply it to the visual:

However, I'm encountering an issue with the output. While the risk slicer functions as expected, utilizing the project slicer causes everything to go blank. I'm also concerned about the scalability of this approach. If I need to incorporate more tables into the logic in the future, this method might become problematic.

A quick note: Splitting the "Action" table into two separate tables is not a viable solution for me.

In summary, I'm seeking alternative approaches to address this challenge. If anyone has suggestions, I would greatly appreciate it.

[lbendlin]

Are "Actions"  and "Risks"  on the same level (independent facts) or do Risks control actions?

[Siboska]

Risk can and must control action. And actions can also be directly controlled by Projects. 

[lbendlin]

Do projects control risks?