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

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,

    • vpnnm's avatar
      vpnnm
      Frequent Visitor
      7 years ago

      Tks for your advice, it is dummy data anyway! 

       

      btw, it’s supposed to calculate the APP(APP_ID) not CIF_NO because 1 CIF can have multiples APP_ID

       

      Also, i would like to use the DATE slider (DISBURSALDATE column of the DISB table) to filter but result is like this:

       

       

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

        Hi vpnnm,

         

        What should the result be?

        I think you need a date table in this scenario. It will connect to three tables just like the Bridge table. Finally, the result will respond to the date slicer that is from the calendar table.

         

         

        Best Regards,