Forum Discussion

Sowrabha_S's avatar
Sowrabha_S
Frequent Visitor
9 months ago
Solved

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 eve...
Need Help
  • v-achippa's avatar
    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

parry2k's avatar
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