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
    )

     

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

Hi Anonymous ,

Please create a calculated column like that to work on it.

 

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

 

 

 

Pbix as attached.

 

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
    )