Forum Discussion

vpnnm's avatar
vpnnm
Frequent Visitor
7 years ago
Solved

Help for calculating multiple fields of multiple table in many to many relationship!

Dear All, Please help me with the situation as below:                         Produce the table as below, however my measure for Total #APP looks wrong, and i spent many day sea...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi vpnnm,

     

    Try this one, please. Are you sure the result should be 2901? I would suggest you check them with a small dataset.

    Total #APP =
CALCULATE (
    DISTINCTCOUNT ( APP[APP_ID_C] ),
    FILTER ( BRIDGE, BRIDGE[ID] <> BLANK () ),
    FILTER ( LEADGEN, LEADGEN[REALNM_NO] <> BLANK () )
)

    Help-for-calculating-multiple-fields-of-multiple-table-in-many-to-many-relationship4

     

    Best Regards,

vpnnm's avatar
vpnnm
Frequent Visitor
7 years ago

Unfortunately, result is same as before.

ps. The bridge table is appended of DISB and APP. 

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

can you share sample rows from each table?

  • vpnnm's avatar
    vpnnm
    Frequent Visitor
    7 years ago

    Yes, here is the file pbix

    \

    • vpnnm's avatar
      vpnnm
      Frequent Visitor
      7 years ago

      is anyone able to figure it out ? please help!

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi vpnnm,

         

        I hope you didn't share anything confidential here. 

         

        Maybe you should count the [CIF_NO] instead. Please refer to the formula and snapshot below.

        Total #APP 2 =
CALCULATE ( DISTINCTCOUNT ( APP[CIF_NO] ), LEADGEN[REALNM_NO] <> BLANK () )

        Help-for-calculating-multiple-fields-of-multiple-table-in-many-to-many-relationship

         

         

        Best Regards,