[DAX Help Needed] How to get the ID of related table into the visual so I can drill through?

[pacifist]

Hi,

please consider the model on the screenshot.

and then consider the visuals and the target state I'd like to achieve:

Few pointers:
- it's the capacity metrics report

- there is WORKSPACES_VW, which is in active relationship with the DATASETS_VW as well as CM_ITEMS_DISTINCT_VW. 

- there is DATASETS_VW table visual, that has some basic properties (DSNs count, CREATOR etc...)
- there is REFRESH_DETAILS_VW that displays the datasets refresh details.

The problem I'm having is that I'm unable to get the DATASETS_VW[ID] field into that table visual so I could be drilling through to datasets details page. The visual says it cannot determine the relationship between the fields.

Can someone please help me to resolve it? Thank you!


[UPDATE]

Can someone explain to me why if I remove the __filterByDatasetId measure, and just add it to the visual filters, then the visual fails to render with "unable to determine the relationship between two or more fields"?

That measure available in the target visual helps me to get the datasetId in there, but it's just so utterly slow compared to when it's not there...
Theat measure looks like this:

_filteredByDatasetId#3 = CALCULATE(
    COUNT(CAPACITY_METRICS_ITEMS_DISTINCT_VW[ITEM_ID]),
    USERELATIONSHIP(DATASETS_VW[ID], CAPACITY_METRICS_ITEMS_DISTINCT_VW[ITEM_ID])
)

[lbendlin]

Can someone explain to me why if I remove the __filterByDatasetId measure, and just add it to the visual filters, then the visual fails to render with "unable to determine the relationship between two or more fields"?

Your tables are not connected in the data model.  You need a measure (any measure) to create a crossjoin.

[pacifist]

thank you. well, they are through that middle table (ITEMS_DISTINCT) via the ITEMID?

[lbendlin]

Follow the arrows. If you can't get there from here and vice versa then that's the same as not being connected. Especially when you are trying to join two fact tables (which you shouldn't do anyway).