empty rows after creating relationships

[Anonymous]

Hi everyone, 

I am using DirectQuery mode to access Catalog and ExecutionLogStorage table in reportServer database, in PowerBI. I filtered the Catalog table just for type=2, which shows just the reports (excluding folders, etc.)

and I am connecting the catalog and ExecutionLogStorage table like this: 

The problem is that, when I create the following table, I have empty rows for Reportname and reportpath. 

How can I get rid of this empty rows?

[amitchandak]

@Anonymous , Default is right to join , It means catalog do no have all the execution log storage has. 

[Anonymous]

@amitchandak when I connect Catalog to ExecutionLog, it means : everything in Cataloga and matching rows from ExecutionLog, like qury below: 

SELECT ca.Name, ca.Path,
count(el.UserName) AS Uses,
el.ReportAction
FROM ExecutionLogStorage el
JOIN dbo.Catalog ca ON el.ReportID=ca.ItemID
where ca.type=2
GROUP BY ca.name, el.ReportAction, ca.path

ORDER BY count(el.UserName) DESC

[dax · Accepted solution]

Hi @Anonymous , 

I think you need to use measure and visual filter to achieve this goal.

Measure = CALCULATE(COUNT(B[NAME]), FILTER(B,B[NAME]=RELATED(A[name])))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.