Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Range of Values in SSAS Tabular model

Hi Friends,   Range of values in SSAS tabular model. i have table name 'Total' and column is [DiffDays] and values in column are like, 100, 200, 300, 400, 500. when i am writing same if statement ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous Are you trying to create a measure or calculated column in SSAS. If you trying to create a calculated column then it will work and i case you are trying to create a measure you need to wrap the column into aggregate function depends on your requirement
    Something like this

     

    Range = 
VAR _datediff = MAX('total'[DiffDay])
RETURN
SWITCH(TRUE()
                ,_datediff<=100,"0-100"
                ,AND(_datediff>100,_datediff<=300),"101-300"
                ,AND(_datediff>300,_datediff<=500),"301-500"
               ,"501-above")

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous What is the error message that you are getting?
Please check the data type of DiffDays it should be whole number. You can try a switch statement as well

Range = SWITCH(TRUE()
                ,total[DiffDays]<=100,"0-100"
                ,AND(total[DiffDays]>100,total[DiffDays]<=300),"101-300"
                ,AND(total[DiffDays]>300,total[DiffDays]<=500),"301-500"
               ,"501-above")
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous 

 

Thanks a lot, great help!!! 🙂

 

solution working as expected.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    need one more help, i was beginner and not able to write complex, below is the requirement.

     

    table[month] values: 1,2,3,4,5,6,7,8,9,10,12.

    in below query no other column mention on filter on month.

    3MonthsDiffrnce = If(Login[month]>0 && Login[month]<4)

    6MonthsDiffrnce = If(Login[month]>3 && Login[month]<7)

    output: 1,2,3 

    output: 4,5,6.

     

    would you please help.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous Can you please explain in more detail. May be you can share some sample data with expected outcome. do you need a calculated column or measure

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Apologies for half cooked information.

         

        below is the table and data.

        date_timeDateDiff_DayDateDiff_Month
        9/4/19 11:51862
        1/19/19 18:1331410
        1/21/19 9:0431210
        12/13/18 17:2935111
        3/25/19 15:172498
        4/1/19 18:042427
        2/8/19 8:502949
        2/19/19 13:202839
        12/18/18 10:5134611
        3/19/19 19:082558

        i want to write new measure based on datediff_month column to divide data into 4 parts as below

        3months, 6months, 12month and 1 year

        3months: 1,2,3

        6months: 4,5,6

        12months: 7,8,9,10,11,12.

        1year: 1,2,3,4,5,6.....11,12.

         

        for that i am writing measure as below.

        3MonthsDiffrnce = If(LastLogin[Diffrnce]>0 && LastLogin[Diffrnce]<4).

         

        this i am implementing in SSAS tabular cube. if possible please suggest anyother method to implement.

        would you please help.