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