Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX-help.

Hi,   Require help in acheving the below count of reference number, sample data set shared below. Similar to below syntax in DAX. if (Month=Jan, logic=0,1,2 Result[Count of Reference Numbers that...
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Here we go!

    Column = 
VAR from1_2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 0, 1, 2 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
VAR from3_7 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 3, 6, 7 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
VAR from5_9 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 5, 8, 9 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Month] = "Jan"
            && 'Table'[Logic] IN { 0, 1, 2 }, from1_2,
        'Table'[Month] = "Feb"
            && 'Table'[Logic] IN { 3, 6, 7 }, from3_7,
        'Table'[Month] = "Mar"
            && 'Table'[Logic] IN { 5, 8, 9 }, from5_9
    )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Year coloum optional and not mandatory

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi Anonymous ,

 

Here we go!

Column = 
VAR from1_2 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 0, 1, 2 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
VAR from3_7 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 3, 6, 7 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
VAR from5_9 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Logic] IN { 5, 8, 9 }
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Month] = "Jan"
            && 'Table'[Logic] IN { 0, 1, 2 }, from1_2,
        'Table'[Month] = "Feb"
            && 'Table'[Logic] IN { 3, 6, 7 }, from3_7,
        'Table'[Month] = "Mar"
            && 'Table'[Logic] IN { 5, 8, 9 }, from5_9
    )