Forum Discussion

freebird28's avatar
freebird28
Icon for Helper I rankHelper 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

  • Hi,

    I'd like to try.  Share the download link of the PBI file.

  • Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • NameMonthMonthNumMonth-2
    JoyJan1Jan
    NitaJan1Jan
    GoksJan1Jan
    JoyFeb2Feb
    SophieFeb2Feb
    SageFeb2Feb
    SerenaMar3Mar
    SophieMar3Mar
    SageMar3Mar
    NitaApr4Apr
    JoyApr4Apr
    PappuApr4Apr
    GoksMay5May
    PappuMay5May
    JoyMay5May
    JadeFeb2Feb
    PaddyMar3Mar
    JaminFeb2Feb

     

    Thank you so much Ashish_Mathur  and lbendlin. I have uploaded the sample data as a table here. So, I am trying to recreate what a Venn diagram would do. Below I show the result I expect as a screenshot. By choosing 2 different months in the slicers, the red box should display the number of names that appear in both months as selected in the slicers. I am able to get the green boxes right. It's the red one that is an issue. So far I have found that if I duplicate the 'Names' table and use the 'month' fields from 2 different tables for the month-slicers then the red box gives me the correct output. I'd like to know if I can acheive the same result without duplicating the table. Thank you. FYI - I have pasted the DAX code for the 'overlap measure' in my earlier post.