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

v-achippa's avatar
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's avatar
v-achippa
Community Support
9 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

  • v-achippa's avatar
    v-achippa
    Community Support
    8 months ago

    Hi @Sowrabha_S,

     

    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