DAX to pull related table artifact's NAME based on TYPE

[pacifist]

Hi,

struggling with DAX, as everytone else 😉

Imagine having a single table of activities for each artifact type ... looks like this

Activity	ArtifactType	ArtifactId
ViewReport	Report	1
ViewReport	Report	1
DeleteReport	Report	2
UpdateDataset	Dataset	1
CreateDataflow	Dataflow	1

and then there are 3 different tables for each artifact type

Reports:

ReportId	ReportName
1	ReportA
2	ReportB
...	...

Datasets

DatasetId	DatasetName
1	DatasetA
...	...

Dataflows:

DaflowId	DataflowName
1	DataflowA
...	...

All table have their relationships setup with the activity table, and are INACTIVE.

Now, what would the DAX look like for the measure that will return the related artifact's NAME in the ACTIVITY_TABLE? 
e.g.:

Activity	ArtifactType	ArtifactId	Name
ViewReport	Report	1	ReportA
ViewReport	Report	1	ReportA
DeleteReport	Report	2	ReportB
UpdateDataset	Dataset	1	DatasetA
CreateDataflow	Dataflow	1	DataflowA

Thank you!

[tamerj1 · Accepted solution]

Hi @pacifist 
Please try the following calculated column

Name =
SWITCH (
    Activities[ArtifactType],
    "Report",
        MAXX (
            FILTER ( Reports, Reports[ReportId] = Activities[ArtifactId] ),
            Reports[ReportName]
        ),
    "Dataset",
        MAXX (
            FILTER ( Reports, Reports[DatasetId] = Activities[ArtifactId] ),
            Reports[DatasetName]
        ),
    "Dataflow",
        MAXX (
            FILTER ( Reports, Reports[DataflowId] = Activities[ArtifactId] ),
            Reports[DataflowName]
        )
)