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

Hi Frank,

 

Appreciate you Prompt efforts,resolved major part of my requirement.

 

i have a few vales in Table [logic] colums like -1,-2,-3....... > i tried by changing data type but getting blank colums.

Planing to add a year coloum so can we can filter based on year

Can you please help me with the DAX syntax for the same.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards,

Santosh

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
    )