Forum Discussion

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Helper IV
3 years ago

Many to Many relationship

Hi everyone,

Based on the below data model Is it possible to modify the below Calculated table (Map_Field) ,Required to add the distinct NPI count from 'SITE_INFO' table based on the matching zipcodes.
'Map_Field' table is a reference of based table that is 'VBI Vaccines'.

Map_Field =
union(
    SUMMARIZE('VBI Vaccines','VBI Vaccines'[HCP_PRIMARY_CLINIC_ZIP_CODE],'VBI Vaccines'[NPI_NUMBER],'VBI Vaccines'[EVENT_DAY],'VBI Vaccines'[HCP_STATE],'VBI Vaccines'[HCP_CITY],"Count",DISTINCTCOUNT('VBI Vaccines'[NPI_NUMBER]),"Entity","UNIQUE HCPs REACHED"),
    CALCULATETABLE(
        SUMMARIZE('VBI Vaccines','VBI Vaccines'[HCP_PRIMARY_CLINIC_ZIP_CODE],'VBI Vaccines'[NPI_NUMBER],'VBI         Vaccines'[EVENT_DAY],'VBI Vaccines'[HCP_STATE],'VBI Vaccines'[HCP_CITY],"Count",DISTINCTCOUNT('VBI   Vaccines'[NPI_NUMBER]),"Entity","UNIQUE HCPs ENGAGED"),'VBI Vaccines'[EVENT_NAME] = "Click_Delivered"))


NOTE:
1. ZIP_CODE is a bridge calculated table.
2. Distinct Zipcode is always more in 'SITE_INFO' whereas, 'VBI Vaccines' table has less distinct zipcodes.

Let me know if any concerns.

Thanks in advance,
SK

@Dax help
@Daxhelp
@powerbi
@related @tables @relations
No RepliesBe the first to reply