Forum Discussion

cathoms's avatar
cathoms
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Can I use DAX measures based on a numeric column to create categories?

So, I am working with a connection to a dataset and the IT folks who maintain the dataset don't want to create new calculated columns everytime someone requests one. Basically, they don't want a ton of custom columns in the dataset. Fair enough. However, there is a numeric column called "CancellationLeadTimeInDays" that is just that - the number of days between when an appointment was scheduled and when it was cancelled. As far as I know, I can't use that in itself as an axis nor legend in a visual.

 

I had been working with a copy of the dataset and I created a column as follows:

 

Cancellation Notice Time =
SWITCH (
    TRUE (),
    Visits[CancellationLeadTimeInDays] >= 92, "More than 3 months",
    Visits[CancellationLeadTimeInDays] >= 61, "2-3 months",
    Visits[CancellationLeadTimeInDays] > 30, "1-2 months",
    Visits[CancellationLeadTimeInDays] > 14, "3-4 weeks",
    Visits[CancellationLeadTimeInDays] > 7, "1-2 weeks",
    Visits[CancellationLeadTimeInDays] > 2, "Within 1 week",
    Visits[CancellationLeadTimeInDays] > 1, "Within 48 hours",
    Visits[CancellationLeadTimeInDays] >= 0, "Within 24 hours",
    Visits[CancellationLeadTimeInDays] < 0, "After appointment",
    "Unknown"
)

 

 

When I was working with the dataset I created the following visual using the "Cancellation Notice Time" column for the Axis (Note that Provider and Other are separate DAX measures):

 

 

I have since been told not to work directly with the dataset but to connect to the dataset, so I now cannot create columns or groups and they won't add the column like above.

 

So, to the question - can and, if so, how do I reproduce the above graph using measures?

 

 

3 Replies