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
    )

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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/439241

 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