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 for support. 

     

    column date type is whole number, and i am using direct column from table.

    in Switch statement, Value is considered as Calculated column or measure, it is not accepting direct column. 

    would you please help in another way.

    Error: not showing column name when i am writing switch DAX. it is showing measure names

    • Anonymous's avatar
      Anonymous
      Not applicable
      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 

     

    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