Count the Values based on dynamic rows in Matrix

[Mharipr5]

We must create a Matrix using "Closed ID" as Rows & Count, Current Account Ratio, and Amount Processed as Values.

Here comes the issue, a few columns are referring to Closed ID & Few of the columns are referring to “Assigned ID”, but we Have to use only the Closed ID as rows & those Columns which are referring the “Assigned ID” Should reflect in the Closed ID Matrix.

I have attached an Excel sample with two tables & and what the result should look like. The result using Closed ID as rows & Column “Count” value is derived based on “Assigned ID”. I'm expecting the same in the  Power Bi matrix using Dax. 

[v-jialluo-msft · Accepted solution]

Hi  @Mharipr5 ,

Please follow these steps:
(1) Create a new measure

COUNTA = CALCULATE(MAX('Table'[Count]),FILTER(ALL('Table'),'Table'[Assigned ID] = MAX('Table 1'[Closed ID])))

(2)Final output

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