Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Duplicate Conditional Column in DAX

Hello folks,

I have this conditional colum in a DataQuery...

So it is grouping figures into units for a graph, It may not be eloquent, but it works for a noob like me.

However, I need to move it over to a DAX generated table, but I'm not having any luck.
I got to this...

Dell Refined Data =
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE( Dell_Warranty, Dell_Warranty[ServiceTag] ),
"StartDate", CALCULATE( MIN( Dell_Warranty[StartDate] ) )
),
"EndDate", CALCULATE( MAX( Dell_Warranty[StartDate] ) )
),
"DaysLeft", CALCULATE( MAX( Dell_Warranty[DaysLeft] ) )
),
"Rounded-Up", if(OR([DaysLeft] < 1,0),
if([DaysLeft] < 101,100),
if([DaysLeft] < 251,250))
)
 
And it kinda works for 100 and 250, but all the zeros are showing as 100, plus the bigger issue is I can only use three arguments, so this is the limit, and I need to get to 1,500. 

Is there a way of getting this working, or another approach that will make this all look very stoopid?

Paul

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    First, create a column in the Dell_Warranty table:

    Rouded-UP = 
SWITCH(
    TRUE(),
    [DaysLeft] = 0, 0,
    [DaysLeft]<= 100, 100,
    [DaysLeft]<= 250, 250,
    [DaysLeft]<= 500, 500,
    [DaysLeft]<= 750, 750,
    [DaysLeft]<= 1000, 1000,
    [DaysLeft]<= 1250, 1250,
    [DaysLeft]<= 1500, 1500
)

    Then, create a calculated table:

    Table = 
SUMMARIZE(
    Dell_Warranty,
    Dell_Warranty[ServiceTag],
    "startDate", CALCULATE( MIN(Dell_Warranty[StartDate]), ALL(Dell_Warranty) ),
    "endDate", CALCULATE( MAX(Dell_Warranty[StartDate]), ALL(Dell_Warranty) ),
    "DaysLeft", MAX(Dell_Warranty[DaysLeft]),
    "Rounded-UP", MAX(Dell_Warranty[Rouded-UP])
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    First, create a column in the Dell_Warranty table:

    Rouded-UP = 
SWITCH(
    TRUE(),
    [DaysLeft] = 0, 0,
    [DaysLeft]<= 100, 100,
    [DaysLeft]<= 250, 250,
    [DaysLeft]<= 500, 500,
    [DaysLeft]<= 750, 750,
    [DaysLeft]<= 1000, 1000,
    [DaysLeft]<= 1250, 1250,
    [DaysLeft]<= 1500, 1500
)

    Then, create a calculated table:

    Table = 
SUMMARIZE(
    Dell_Warranty,
    Dell_Warranty[ServiceTag],
    "startDate", CALCULATE( MIN(Dell_Warranty[StartDate]), ALL(Dell_Warranty) ),
    "endDate", CALCULATE( MAX(Dell_Warranty[StartDate]), ALL(Dell_Warranty) ),
    "DaysLeft", MAX(Dell_Warranty[DaysLeft]),
    "Rounded-UP", MAX(Dell_Warranty[Rouded-UP])
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Can you share sample data and sample output.

     

    Mark me @

    Appreciate your Kudos.