Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have three tables.
1. Order Facts with a date column called First Published. This stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.
2. Work Records with a date column called First Assigned. This stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.
3. A link table called Unique Work Records. This is a two column table with all the unique work records by OrderID. This column is then used to link to the other two tables. The second column is First Assigned and is produced using a RELATED() function to extract the MIN([First Assigned]). Again, this stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.
I have then created three measures:
My goal is to have this final measure give the number of days between the other two dates and it does seem to work however it will only return the value as a date. When I go to the modelling ta for that measure I cannot select a different data type so I cannot show it as a whole number i.e. the number of days.
Can anyone help?
Thanks.
Solved! Go to Solution.
Try adding a "*1" to your formula for the final measure and see if that helps:
Time to Delegation = ([First Published Date Max] - [First Assigned Date Max]) * 1
Or use DATEDIFF with an interval of DAYS:
https://msdn.microsoft.com/en-us/library/dn802538.aspx
Try adding a "*1" to your formula for the final measure and see if that helps:
Time to Delegation = ([First Published Date Max] - [First Assigned Date Max]) * 1
Or use DATEDIFF with an interval of DAYS:
https://msdn.microsoft.com/en-us/library/dn802538.aspx
@Greg_Deckler the "*1" worked great. If you dn't mind, why does that work?
Thank you.
Forces it to be returned as a number instead of a date type, basically in DAX data type logic:
Date - Date = Date whereas Date * Number = Number
Very useful. Thanks.
Forces it to be returned as a number instead of a date type, basically in DAX data type logic, Date - Date = Date whereas Date * Number = Number
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |