Forum Discussion
Help for calculating multiple fields of multiple table in many to many relationship!
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,
Microsoft EmployeeHi 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,
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,
anyone?
- Stachu
Community Champion
can you share the data snapshot from each table (can be just few rows each) with the expected results for that sample?
Thank you for your effort.
However, the Total #App still look wrong ,right?
- v-jiascu-msft
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,
Thank you so much, i learned a valuable lesson.
