Help with DAX

Hello All,

I need help with DAX.

I have 3 columns in my data set. BU,EmailId and End Date. I also have a dropdown on End Date in the dashboard. I need to calculate the count of email ids for every business unit between Jan 1st 2025 and the selected value of end date from the dropdown. I also need to count the the number of email ids with 1 count, 2 counts and 3 counts (frm the above result set) as 3 seperate measures and display in the table as Business Unit, 1 Click count, 2 Click Count and 3 Click Count.

Currently I am following this approach:

First Measure:

Total Count =

VAR SelectedEndDate =

SELECTEDVALUE(RO[END_DATE]) -- Or DateTable[Date] if slicer is from a date table

VAR StartDate =

DATE(2025, 1, 1)

RETURN

CALCULATE(

COUNT(RO[EMAIL_ADDRESS]),

ALL(RO[END_DATE]),

RO_CLICKERS[END_DATE] >= DATE(2025,1,1) &&

RO_CLICKERS[END_DATE] <= SelectedEndDate)

2nd measure:

Onetimeclickers =

CALCULATE (COUNTROWS(FILTER(VALUES(RO_CLICKERS[EMAIL_ADDRESS]), [Total Count] = 1)))

TwoTimeClickers =

CALCULATE (COUNTROWS(FILTER(VALUES(RO_CLICKERS[EMAIL_ADDRESS]), [Total Count] = 2)))

ThreeTimeClickers =

CALCULATE (COUNTROWS(FILTER(VALUES(RO_CLICKERS[EMAIL_ADDRESS]), [Total Count] = 3)))

This is giving the counts only for the selected month from the dropdown (for ex: Mar 31st 2025) not between Jan 1st and March 31st.

Example screenshot for reference:

Appreciate your help!

Need Help

v-achippa
9 months ago
Hi Sowrabha_S,

Thank you for reaching out to Microsoft Fabric Community.

Thank you d_m_LNK and parry2k for the prompt response.

Thank you for confirming that all the fields come from the same table.
Here the issue is happening because the Total Count measure is still limited by the slicer context, it does not clear the filter properly when you also use the same table for the date filter.

Please use this below measure:

Total Count =

VAR SelectedEndDate = SELECTEDVALUE(RO[END_DATE])

VAR StartDate = DATE(2025,1,1)

RETURN

CALCULATE(

    COUNTROWS(RO),

    REMOVEFILTERS(RO[END_DATE]),

    RO[END_DATE] >= StartDate &&

    RO[END_DATE] <= SelectedEndDate

)

Now use your existing measures like this:

OneTimeClickers =

COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 1))

TwoTimeClickers =

COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 2))

ThreeTimeClickers =

COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 3))

This will correctly counts each email between jan 1 2025 and the selected end date from the slicer and gives you the correct results.

Thanks and regards,

Anjan Kumar Chippa

6 Replies

parry2k

Super User

9 months ago

Sowrabha_S if you are using latest Power BI version then you can create a function as shown below and pass value for each measure to get the count:

DEFINE 
    Function fnCountClick = ( cnt : scalar ) =>
    VAR __BU = SELECTEDVALUE ( Tbl1[BU] )
    VAR __Tbl = 
        SUMMARIZECOLUMNS ( 
            Tbl1[Email], 
            Tbl1[BU] ,
            "@Cnt", [Total Count]
        )
    RETURN 
        COUNTROWS ( 
            FILTER ( 
                __Tbl, 
                [BU] = __BU 
                && [@Cnt] = cnt 
            ) 
        )


//measure 1 count
Measure 1 = fnCountClick( 1 )

//measure 2 count
Measure 2 = fnCountClick( 2 )

//measure 3 count
Measure 3 = fnCountClick( 3 )

read more about functions here Using DAX user-defined functions (preview) - Power BI | Microsoft Learn

d_m_LNK
Super User
9 months ago
I think the issue may be your Count and ALL statements on why you are only getting the selected dates. In your Dax you reference the RO Table to count the email addresses but filter the dates by the RO_Clickers table.
I think the filtering issue is because you are using the ALL function on a different table than the date references later in the DAX query.

Is there an EmailID column you can count that exists in the RO_clickers table? Could you send a screenshot of your datamodel?
Sowrabha_S
Frequent Visitor
9 months ago
It is actually the same table (RO or RO_Clickers). All fields are coming from the same table. Sorry for the confusion!
- v-achippa
  Community Support
  9 months ago
  Hi Sowrabha_S,
  
  Thank you for reaching out to Microsoft Fabric Community.
  
  Thank you d_m_LNK and parry2k for the prompt response.
  
  Thank you for confirming that all the fields come from the same table.
  Here the issue is happening because the Total Count measure is still limited by the slicer context, it does not clear the filter properly when you also use the same table for the date filter.
  
  Please use this below measure:
  
  Total Count =
  
  VAR SelectedEndDate = SELECTEDVALUE(RO[END_DATE])
  
  VAR StartDate = DATE(2025,1,1)
  
  RETURN
  
  CALCULATE(
  
      COUNTROWS(RO),
  
      REMOVEFILTERS(RO[END_DATE]),
  
      RO[END_DATE] >= StartDate &&
  
      RO[END_DATE] <= SelectedEndDate
  
  )
  
  Now use your existing measures like this:
  
  OneTimeClickers =
  
  COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 1))
  
  TwoTimeClickers =
  
  COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 2))
  
  ThreeTimeClickers =
  
  COUNTROWS(FILTER(VALUES(RO[EMAIL_ADDRESS]), [Total Count] = 3))
  
  This will correctly counts each email between jan 1 2025 and the selected end date from the slicer and gives you the correct results.
  
  Thanks and regards,
  
  Anjan Kumar Chippa
  - v-achippa
    Community Support
    8 months ago
    Hi Sowrabha_S,
    
    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
    
    Thanks and regards,
    
    Anjan Kumar Chippa

Forum Discussion

Help with DAX

6 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX