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
Hello All
I have a complex request to be able to select a sample of data for auditing based on user-selected dates.
What I want to be able to do is:
I am particularly struggling around Step 3, which I am trying to do it in a column as I would need this in to show the total customers by Weeks Range a clustered chart.
You can see above that Total Days from the selected date is working which is taking the date diff from the clockstartdate and User-selected date. In Weeks = Total Days/7.
Is it possible to create a Column that’s based on the “In weeks” measure to show this is ‘0-1 Weeks’ range ?
Any help would be much appreciated.
Solved! Go to Solution.
Hi, @Anonymous
This can be a little difficult if your model involves slicers.
You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.
Best Regards,
Community Support Team _ Eason
Any help would be much appreciated guys! thank you
@tackytechtom I tried to replicate your logic in a Column but it showing me the wrong range, especially when you have -ve In Weeks figures - see below. Are you able to share your PBIX file please? @tackytechtom
@amitchandak thank you for your quick response. So your suggestion works fine in Measure. Is it possible to Group the Measure week into a range? For example say if the Measure week is returning 0.43, then Weeks Range as 0-1 Weeks?
I need this in a column so that I can create a clustered Chart, total customers by Week Range - something like below.
Hi @Anonymous ,
I have a first draft here:
The question is though what to do when it exactly hits a week (or a multiple of that, see 7 in picture). Should it then show 6-7 Weeks or 7-8 Weeks? You might be able to tweak the DAX below accordingly, but this might be a start for you:
TomsGroupingWeekTable = ROUNDDOWN( Table[In Weeks], 0 ) & "-" & ROUNDUP( Table[In Weeks], 0 ) & " Weeks"
Let me know if you need further assistance!
/Tom
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
@tackytechtom Well actually, I added a IF statement to deal with the -ve In Weeks figures but it didnt go well for other In Weeks figures.
You can see below, User selected date is 31/07/2021, based on that you can see there should be a range from 0-1 Weeks, 1-2 Weeks, and 2-3 Weeks etc.. but its shows as 0-1 Weeks for all.
This is my Dax code
@tackytechtom, not sure if I am missing something obvious, any help would be much appreciated.
Many thanks
Hi, @Anonymous
This can be a little difficult if your model involves slicers.
You are using a calculated column instead of a measure, which will cause the result to be static(not recalculated based on your current filter context). If you use measure instead, the result will be correct, but you won't be able to apply it to the 'Axis' on the bar chart.
Best Regards,
Community Support Team _ Eason
Thank you @v-easonf-msft .I suspect this might be the case. Would be good to enable this feature in the Power BI.
@Anonymous , Try measure like
Measure =
var _min = minx(allselected(Date),Date[Date])
return
Averagex(Table, datediff(Table[clock Start date], _min, day) )
Measure Week=
var _min = minx(allselected(Date),Date[Date])
return
Averagex(Table, datediff(Table[clock Start date], _min, day)/7 )
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 |
---|---|
24 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
24 | |
23 |