Forum Discussion

greenguy2012's avatar
greenguy2012
Advocate I
8 years ago
Solved

DAX: Count where row value equals filter context

Hey there,

 

New to DAX so any help is appreciated. My goal is to understand how many customers cancel in a given sold date. Basically how many items did we sell in a given time period vs how many cancelled in that same period. 

 

What I'm trying to do is count the number of rows where [Cancel Date] column values equal the Pivot Table Row Context Date [Sold Date] grouped by Month. Ex Grouped by Jan Sold Month, 10 cancelled in same Jan period. 

 

Needed Pivot Table output:

 

Sold Month. |  Cancel Count

- Jan.  |   10

- Feb   |  15

- Mar   |  20

 

Hopefully thats clear. Thanks in advance for your help!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi greenguy2012,

     

    You can new a calculated column similar to:

    Cancel Count =
CALCULATE (
    COUNT ( Table[Cancel Date] ),
    FILTER (
        ALLEXCEPT ( Table, Table[Sold Date].MONTH ),
        [Cancel Date] = [Sold Date]
    )
)

     

    Add [Sold Date].Month and above column into table visual.

     

    Regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi greenguy2012,

     

    You can new a calculated column similar to:

    Cancel Count =
CALCULATE (
    COUNT ( Table[Cancel Date] ),
    FILTER (
        ALLEXCEPT ( Table, Table[Sold Date].MONTH ),
        [Cancel Date] = [Sold Date]
    )
)

     

    Add [Sold Date].Month and above column into table visual.

     

    Regards,

    Yuliana Gu