DAX-help.

[Anonymous]

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 are in(Logic 0,1,2)]
            elseif Month=Feb,Logic=3,6,7 Result[Count of Reference Numbers that are in(Logic 5,6,7)]
           elseif Month=March,Logic=5,8,9 Result[Count of Reference Numbers that are in(Logic 5,6,7)]
             .......
    ),Blank())

Regards,

Sam

[v-frfei-msft]

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]

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]

Year coloum optional and not mandatory

[v-frfei-msft · Accepted solution]

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
    )

[amitchandak]

Create a new column like, and then do count in visual

if (table[Month]="Jan" &&  table[logic] in {0,1,2} , table[Reference Numbers],
            if (table[Month]="Feb" &&  table[logic] in {3,4,5} , table[Reference Numbers]    ),Blank())

Try using SWITCH : https://community.powerbi.com/t5/Desktop/Help-with-SWITCH-TRUE-with-multiple-items-to-evaluate/td-p/...

 Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin