Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I'm trying to recreate a Conditional Column I've got on another report but as a DAX measure. I'm unable to replicate it in Power Query so I'm trying to create it in measure form
Does anybody know what to do? I'm at a loss
Cheers,
Mike
Solved! Go to Solution.
Hi Micheal
I feel you would be better using a M query
and look up the time of day with a relationship.
But if you must use a DAX measure then try this solution ….
Time of day =
// get a time as scalar vaue
Var mytime = MIN(Times[Time])
// get the hour element of the time
Var myhour= HOUR(mytime)
RETURN
SWITCH(
TRUE(),
myhour < 11, "Morning",
myhour < 14, "Afternoon",
myhour < 17, "Late Afternoon",
myhour < 20, "Evening",
BLANK()
)
The big draw back of this soultion is that DAX measures need a scalar, which is why a M query and lookup might be a better solution.
Hi Micheal
I feel you would be better using a M query
and look up the time of day with a relationship.
But if you must use a DAX measure then try this solution ….
Time of day =
// get a time as scalar vaue
Var mytime = MIN(Times[Time])
// get the hour element of the time
Var myhour= HOUR(mytime)
RETURN
SWITCH(
TRUE(),
myhour < 11, "Morning",
myhour < 14, "Afternoon",
myhour < 17, "Late Afternoon",
myhour < 20, "Evening",
BLANK()
)
The big draw back of this soultion is that DAX measures need a scalar, which is why a M query and lookup might be a better solution.
Perfect, thank you very much
If you have null values you are gonna get errors. Start testing the null values.
@michael_knight Me too, that looks like you are in Power Query but you say you are not??? There are no measures in Power Query, only calculated columns.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Sorry, I don't think I worded it correctly. I have a report which I created the Conditional Column in Power Query, I want to use the same theory with the Times and Late, Morning etc, but in a DAX Measure instead of a Conditional Column. Does that make sense?
Cheers,
Mike
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |