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

[vpnnm]

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 searching without possible solution:

Result

 

Date Slide from the DISB table.

CMP_NM column from the LEADGEN table

My Dax code as follow:

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

Total #DISB = CALCULATE(DISTINCTCOUNT(DISB[agreementid]),LEADGEN[REALNM_NO]<>BLANK())

Total #LEAD = CALCULATE(COUNT(LEADGEN[CUST_NO]),ALLEXCEPT(LEADGEN,LEADGEN[CMP_NM]))

Your help are greatly appreciated.
Thank you so much,

[vpnnm]

Please help or point me to some dỉection so i can figure it out. Tks

[Stachu]

do numbers change  to correct ones if you switch the relationship to single direction for APP and DISB tables?

[vpnnm]

Unfortunately, result is same as before.

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

[Stachu]

can you share sample rows from each table?

[vpnnm]

Yes, here is the file pbix

\

[vpnnm]

is anyone able to figure it out ? please help!

[v-jiascu-msft]

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 () )

Best Regards,

[vpnnm]

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]

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,

[vpnnm]

hi,

i try to achieve the result as below with your suggestion of making DATE table  but still doesnt work. Thank you so much for your further supports.

[v-jiascu-msft]

Hi @vpnnm,

I found something after digging into your model. 

1. The Creation_Date is a Datetime type. We need to convert it into a Date type column first by adding a new column.

Creation_Date_New = [CREATION_DATE].[Date]

2. Create a proper relationship like below. I think the one below should be the one in your scenario.

3. The field of the slicer should be from the Calendar table.

Best Regards,

[vpnnm]

Thank you for your effort.

However, the Total #App still look wrong ,right?  

[v-jiascu-msft · Accepted solution]

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 () )
)

Best Regards,

[vpnnm]

Thank you so much, i learned a valuable lesson.

[vpnnm]

anyone?

[Stachu]

can you share the data snapshot from each table (can be just few rows each) with the expected results for that sample?