[DAX] values from table in inactive relationship

[pacifist]

Hi, I'm sure this will be a piece of cake for the SMEs here.

Reports Table (reports_vw):

ReportName	ID
report_a	1
report_b	2
report_c	3

Metrics Table (metrics_vw):

ITEM_ID	cpu_perc
2	0.0012

Reports table and Metrics Table are in inactive relationships through ID/ITEM_ID columns.
I'd like a result similar to this one:

ReportName	ID	cpu_perc
report_a	1
report_b	2	0.0012
report_c	3

I've tried to create a following dax:

_filteredByReportId = CALCULATE(
    DISTINCTCOUNTNOBLANK(metric_vw[ITEM_ID]),
    USERELATIONSHIP(metrics_vw[ITEM_ID], reports_vw[ID])
)

 when I add the above as a cross filter into the table visual, I get only a record for the report_b.

Helo is much appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Link to learn this: Show items with no data in Power BI - Power BI | Microsoft Learn

When you right-click one of your columns, there is an option to select "Show items with no data"

[pacifist]

Hi 

interesting solution. I however have another question (how can I upload the file!?). Once you setup a new page, with the drill through using the ITEM_ID from the METRICS table, how do I achieve something like below so I can drill through to that page while retaining all rows?

I will then answer this as marked though I admit I'm expanding the question. Thank you!

[Jihwan_Kim]

Hi,

I am not 100% sure if I understood your question, but I think you can try to create active relationship.

[pacifist]

You understood perfectly, but I'm unable to activate it. I have other artifact types connecting to the same table!

If you resolve this I will buy you a beer 😉