Calculated column from one table to another table

[sabilahmed]

Hello,

I desperately need help with the following problem.

I have a calculated column in table "Config" which counts the number of instances per IV_Product_RecID (as there are "many" rows with multiple instances of IV_Product_RecID):

Count_IV_Product_RecID =

    CALCULATE(

        COUNT(Config[IV_Product_RecID]

            ),

            FILTER(

                Config,

                Config[IV_Product_RecID] = EARLIER(Config[IV_Product_RecID])

            )

    )

Then I have another table v_rpt_product with only "one" row per IV_Product_RecID. I have made sure there is a one-many relationship beween the two tables as depicted below:

I need to add a calculated column in v_rpt_product telling me the number of times/ instances IV_Product_RecID shows up in the Config table.

I tried to "merge" the two tables so I could "expand" to return the calculated column from Config but this did not work as the merge feasture will not return the calculated column. Basically this would have worked in excel like a vlookup.

Please help me!

Thanks in advance,

S

[sabilahmed · Accepted solution]

I've done it:

Created a calculated column in v_rpt_product:

Count_IV_Product_RecID = COUNTROWS(RELATEDTABLE(Config))

[Anonymous]

Have you tried merge queries in power query?

[sabilahmed]

Yes - I mentioned that above, but it didn't work. Due to the nature of the calculation it doesn't appear as one of the columns to expand in the merge.

I'm thinking a better way to solve this issue is if I rephrase my questions to this:

How can I create a calculated column in my Config table to return the count of IV_Product_RecID from v_rpt_product table?

Remember:

Config has MANY ID's

v_rpt_product has UNIQUE ID's per row

Therefore this is a ONE-MANY relationship.