Dax to calculate distinctcount of IDs for below scenario

[komaldhabhai]

Hi,
I need help on the below mentioned scenario:

I have a 'Period' filter having values (YTD, Current Month)

I have a 'Date' filter having only end date selection (Before type date filter)

How these two filter work:
When I select YTD and select an end date say 1 Nov 2024 from date filter then my date range for calculations would be 1 Jan 2024 to 1 Nov 2024
When I select Current Month and select date say 1 Nov 2024 from date filter then my date range for calculations would be for one whole month of Nov 2024.

I want to calculate the distinct count of ContactIDs that fall in the period selected , having both Channeltype .i.e Digital and In Person
After calculating this I want to show distribution of these IDs among Channels.

Example:

Period selected : YTD
Date selected : 1 Dec 2024
(Now the date range for calculations would be from 1 Jan 2024 to 1 Dec 2024)

According to data,
ContactIDs A1234 , B0171 and C9856 have both Channeltype(.i.e Digital and In Person) in the period selected

My distinct count of IDs is 3

Now I want to see how these 3 IDs are distributed among Channel

Marketing - 2 (ContactIDs A1234 ,C9856 falls under Marketing so count would be 2)
Accounting - 1 (ContactID B0171 falls under Accounting so count would be 1)
Finance - 3 (ContactIDs A1234 , B0171 and C9856 falls under Finance so count would be 3)
Sales - 0 (No ID which has both Channeltype)
Admin - 1 (ContactID C9856 falls under Accounting so count would be 1)

ContactID	Date	ChannelType	Channel	ClientSegment
A1234	10/1/2024	Digital	Marketing	HT
B0171	5/1/2024	In Person	Accounting	RY
C9856	11/1/2024	In Person	Admin	ZY
C9856	5/1/2024	Digital	Finance	ZY
B0171	1/1/2024	Digital	Finance	RY
D0476	7/1/2024	Digital	Finance	HT
A1234	10/1/2024	In Person	Finance	HT
E0537	12/1/2024	Digital	Sales	RY
C9856	10/1/2024	Digital	Marketing	ZY

[Bibiano_Geraldo · Accepted solution]

Hi @komaldhabhai ,

To achieve your goal, first create a new disconected calendar table by this DAX:

Date = 
CALENDAR(
    MIN('Table'[Date]),
    MAX('Table'[Date])
)

Make sure that you have the period table, if not, please consider to create a new disconnected table for period by this DAX:

Period = {"YTD", "Current Month"}

And rename the column for Period also:

Now create DAX measure to count Distinct Contacts with MultipleChannels:

Distinct_Contacts_Multiple_Channels = 
VAR selectedDate = LASTDATE('Date'[Date]) 
VAR PeriodSelection = SELECTEDVALUE('Period'[Period])

VAR startDate = 
    IF(
        PeriodSelection = "YTD", 
        DATE(YEAR(selectedDate), 1, 1), 
        DATE(YEAR(selectedDate), MONTH(selectedDate), 1)
    )

VAR endDate = 
    IF(
        PeriodSelection = "YTD", 
        selectedDate, 
        EOMONTH(selectedDate, 0)
    )


VAR Contact_Channel_Count =
    ADDCOLUMNS(
        ALL('Table'[ContactID]),
        "ChannelCount", 
        CALCULATE(
            DISTINCTCOUNT('Table'[ChannelType]),
            DATESBETWEEN('Table'[Date], startDate, endDate)
        )
    )

RETURN
CALCULATE(
    DISTINCTCOUNT('Table'[ContactID]),
    FILTER(Contact_Channel_Count, [ChannelCount] > 1),
    DATESBETWEEN('Table'[Date], startDate, endDate)
)

And other measure for channel distribuitions:

MultiChannel_Distribution = 
VAR SelectedDate = LASTDATE('Date'[Date])
VAR PeriodSelection = SELECTEDVALUE('Period'[Period])
VAR StartDate =
    IF(
        PeriodSelection = "YTD",
        DATE(YEAR(SelectedDate), 1, 1),
        DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
    )
VAR EndDate =
    IF(
        PeriodSelection = "YTD",
        SelectedDate,
        EOMONTH(SelectedDate, 0)
    )
    
// Get all ContactIDs in the date range ignoring channel filters
VAR AllContacts =
    CALCULATETABLE(
        VALUES('Table'[ContactID]),
        REMOVEFILTERS('Table'[ChannelType], 'Table'[Channel]),
        DATESBETWEEN('Table'[Date], StartDate, EndDate)
    )

// From all contacts, filter those having more than one distinct ChannelType (i.e. both Digital and In Person)
VAR ValidContacts =
    FILTER(
        AllContacts,
        CALCULATE(
            DISTINCTCOUNT('Table'[ChannelType]),
            REMOVEFILTERS('Table'[ChannelType], 'Table'[Channel]),
            DATESBETWEEN('Table'[Date], StartDate, EndDate)
        ) > 1
    )

// Now count, in the current filter context (which will include a specific Channel if used in a visual),
// the distinct ContactIDs that are in the ValidContacts list.
RETURN
CALCULATE(
    DISTINCTCOUNT('Table'[ContactID]),
    TREATAS(ValidContacts, 'Table'[ContactID]),
    DATESBETWEEN('Table'[Date], StartDate, EndDate)
)

Now add to slicers to your report, one with period column and other with date column from date table:

Now add a new card and place Distinct_Contacts_Multiple_Channels measure in the field:

Now add a table visual and add the channels columns and the MultiChannel_Distribution measure:

this will be the final rsult if you select 01 dec 2024:

[komaldhabhai]

Hey @Bibiano_Geraldo Thanks! It worked.

[FarhanJeelani]

Dear @komaldhabhai ,

Please try below dax:

-- DAX Measure to calculate the distinct count of ContactIDs having both Digital and In Person channel types
DistinctContactIDsBothChannels =
VAR SelectedEndDate = MAX('Date'[Date])
VAR SelectedPeriod = SELECTEDVALUE('Period'[Period])
VAR StartDate =
    IF(
        SelectedPeriod = "YTD", DATE(YEAR(SelectedEndDate), 1, 1),
        DATE(YEAR(SelectedEndDate), MONTH(SelectedEndDate), 1)
    )
VAR FilteredData =
    FILTER(
        'ContactData',
        'ContactData'[Date] >= StartDate && 'ContactData'[Date] <= SelectedEndDate
    )
VAR ContactsWithBothChannels =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(FilteredData, 'ContactData'[ContactID]),
            "ChannelCount",
            CALCULATE(
                DISTINCTCOUNT('ContactData'[ChannelType]),
                FilteredData,
                'ContactData'[ContactID] = EARLIER('ContactData'[ContactID])
            )
        ),
        [ChannelCount] = 2
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT('ContactData'[ContactID]),
        ContactsWithBothChannels
    )

-- Measure to show distribution of these ContactIDs among Channels
ChannelDistribution =
VAR SelectedEndDate = MAX('Date'[Date])
VAR SelectedPeriod = SELECTEDVALUE('Period'[Period])
VAR StartDate =
    IF(
        SelectedPeriod = "YTD", DATE(YEAR(SelectedEndDate), 1, 1),
        DATE(YEAR(SelectedEndDate), MONTH(SelectedEndDate), 1)
    )
VAR FilteredData =
    FILTER(
        'ContactData',
        'ContactData'[Date] >= StartDate && 'ContactData'[Date] <= SelectedEndDate
    )
VAR ContactsWithBothChannels =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(FilteredData, 'ContactData'[ContactID]),
            "ChannelCount",
            CALCULATE(
                DISTINCTCOUNT('ContactData'[ChannelType]),
                FilteredData,
                'ContactData'[ContactID] = EARLIER('ContactData'[ContactID])
            )
        ),
        [ChannelCount] = 2
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT('ContactData'[ContactID]),
        FilteredData,
        ContactsWithBothChannels
    )

Please mark this as solution if it helps you. Appreciate Kudos.