Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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]
)
)
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]
)
)
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |