Forum Discussion
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?
If you're using the standard live connection to the dataset, then I think you're out of luck since you can't use a measure as a dimension.
However, if you can connect to the dataset with DirectQuery, then it's possible to do local modeling without modifying the underlying dataset, so you can get add back the calculated column.
Please refer to the following for more information on using DirectQuery for Power BI datasets:
3 Replies
- AlexisOlson
Super User
If you're using the standard live connection to the dataset, then I think you're out of luck since you can't use a measure as a dimension.
However, if you can connect to the dataset with DirectQuery, then it's possible to do local modeling without modifying the underlying dataset, so you can get add back the calculated column.
Please refer to the following for more information on using DirectQuery for Power BI datasets:
- cathoms
Responsive Resident
Nice feature. Thanks - that should do the trick!
- Ashish_Mathur
Super User
Hi,
This is possible with mesures. I will need a PBI file to work with. Ensure that you have the Other and Provider measures already written on that file.