Count lines in History table and sum against current table

[cran]

Hi,

I have 2 fact tables in my model. fact1 that has one line per uniqueid for a project and fact2 that has one line per phase change in each project so history of project. They look as below and are connected by an inactive relationship on unique project id (1 to many) as the active relationships are to all the Dimension tables shared between them.

I would like to count how many checks there are per unique project id in the fact2 history table and bring it back as a count in the fact 1 table as below.

At the moment I have created a summarised table that filters on the history table where the checking status is not blank then counts the rows and sums it against the projectuniqueids. I think link this table to fact1 table and lookup the value using related(). 

I'm wondering if there's a dax function that can do this all in one go so I don't have duplicate summarised tables in the model?

I tried creating the calculated table as a variable but then I can't work out how to access a column in a table variable in the dax

Thank you

fact1project

Projectuniqeid	startdate	enddate	Total check counts	External check count	Internal check count
1			2	1	1
2			2	2	0
3

Fatct2projecthistory

Projecthistoryuniqiueid	Projectuniqueid	projecttransitionid	Projectcheckingstatus
A	1	1	externalcheck
B	1	2	internalcheck
C	1	3
D	2	1	externalcheck
E	2	3
F	2	4	externalcheck

[cran]

Thank you @123abc exactly what I needed

[123abc · Accepted solution]

To achieve this in Power BI using DAX, you can create a calculated column in the "fact1project" table that counts the number of checks from the "fact2projecthistory" table for each unique project ID. You can use the SUMX and FILTER functions to perform this calculation. Here's the DAX formula to create the calculated column:

```DAX
Total Check Counts =
VAR ProjectUniqueID = 'fact1project'[Projectuniqueid]
RETURN
COUNTROWS(
FILTER(
'fact2projecthistory',
'fact2projecthistory'[Projectuniqueid] = ProjectUniqueID
&& NOT ISBLANK('fact2projecthistory'[Projectcheckingstatus])
)
)
```

This formula does the following:

1. It first captures the current project's unique ID in the `ProjectUniqueID` variable.

2. Then, it uses the `FILTER` function to filter the "fact2projecthistory" table for rows where the project unique ID matches the current project (`'fact2projecthistory'[Projectuniqueid] = ProjectUniqueID`) and where the checking status is not blank (`NOT ISBLANK('fact2projecthistory'[Projectcheckingstatus])`).

3. Finally, it counts the rows returned by the `FILTER` function using `COUNTROWS`, which gives you the total check counts for the project.

After creating this calculated column in the "fact1project" table, you can use it in your visuals to display the total check counts for each project. This way, you won't need duplicate summarized tables, and the calculation will be performed on the fly based on your data model relationships.