Forum Discussion

freebird28's avatar
freebird28
Helper I
1 year ago
Solved

Overlap Count Measure

This is my main table that has Names associated with each month. 

 

I need to find the individual monthly counts of names and the intersection of names between any 2 months chosen on the slicer. I have duplicated the "month" column and have 2 slicers for month 1 and month 2. Month 1 filters only KPI card 1 and Month 2 filters only KPI card 2.

 

There is a 3rd KPI card that should show how many names overlap between Jan and Feb. This KPI card is affected by the values chosen in both slicers (month 1 and month 2). Here is my overlap measure. 

 

TEST Overlap = 
VAR SelectedMonth1 = SELECTEDVALUE('NI Table'[Month], BLANK())
VAR SelectedMonth2 = SELECTEDVALUE('NI Table'[Month-2], BLANK())

// Ensure that both selected months are valid
RETURN
IF(
    ISBLANK(SelectedMonth1) || ISBLANK(SelectedMonth2),
    BLANK(),
    
    // Calculate names specific to each month contextually
    VAR NamesInMonth1 =
        CALCULATETABLE(
            VALUES('NI Table'[Name]),
            'NI Table'[Month] = SelectedMonth1
        )
    
    VAR NamesInMonth2 =
        CALCULATETABLE(
            VALUES('NI Table'[Name]),
            'NI Table'[Month] = SelectedMonth2
        )
    
    // Count overlapping names
    VAR OverlapCount =
        COUNTROWS(
            INTERSECT(NamesInMonth1, NamesInMonth2)
        )
    
    // Return the overlap count
    RETURN
        OverlapCount
)

 

The problem is "overlap measure" returns blank. The VAR selectedmonth1 and selectedmonth2 (defined inside the measure) both return blank in this measure. If I choose the same month (say, Jan) in both slicers then the KPI card shows the count for that month (Jan, in our example), else it shows blank when 2 different months are chosen on the slicers. Could someone please help me where I could be making a mistake or what I could do different?

 

Thank you so much!

 

8 Replies