Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've been trying to figure this out for a day or so now. Some messages on this forum have gotten me closer but I'm still not quite there.
What I've got is a table (obviously) full of rows (obviously) that represent work items. One column is called "Changed Date" which is a datetime stamp of the last time this work item has been modified. This is connected to live data and so is updatable upon refresh.
What I want is to be able to categorize that data according to how far away that date is from today I want categories for within 10 days ago, 11-20 days ago, 21-30 days ago, and >30 days ago. These need to be static - I don't want the visualization redefining the bin size every time the data refreshes, I want it resorting the data into the proper bins.
What I've tried is a whole host of things, all of which involve me using the work item's unique ID in the "values" section of the vizualization, and some representation of the date column in the "Legend" section. I can't remember everything I've tried, but here are the highlights:
Thank you in advance for any advice and please let me know if I can clarify anything for anybody.
Solved! Go to Solution.
Hi @Zelbinian,
You can create the column in M language or Dax then just use this column to make you visuals
M Language (query editor) if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 10 then "10 days ago" else if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 10 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 20 then "20 days ago" else if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 20 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 30 then "30 days ago" else "> 30 day" DAX Bin = VAR Date_Select = DATEDIFF ( Table1[Date]; TODAY (); DAY ) RETURN SWITCH ( TRUE (); Date_Select <= 10; "10 days ago"; Date_Select > 10 && Date_Select <= 20; "20 days ago"; Date_Select > 20 && Date_Select <= 31; "30 days ago"; "> 30 days" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Zelbinian,
You can create the column in M language or Dax then just use this column to make you visuals
M Language (query editor) if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 10 then "10 days ago" else if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 10 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 20 then "20 days ago" else if Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) > 20 and Duration.Days(Date.From(DateTime.LocalNow()) - [Date]) <= 30 then "30 days ago" else "> 30 day" DAX Bin = VAR Date_Select = DATEDIFF ( Table1[Date]; TODAY (); DAY ) RETURN SWITCH ( TRUE (); Date_Select <= 10; "10 days ago"; Date_Select > 10 && Date_Select <= 20; "20 days ago"; Date_Select > 20 && Date_Select <= 31; "30 days ago"; "> 30 days" )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI ended up using nested ifs in DAX, but I like that you figured out the switch statement. Can you explain how that switch statement works? That's a little different than the exemplars used in the Power BI documentation.
Hi @Zelbinian,
You can also simplify the function by doing this:
Bin = VAR Date_Select = DATEDIFF ( Table1[Date]; TODAY (); DAY ) RETURN SWITCH ( Date_Select > 0 ; Date_Select <= 10; "10 days ago"; Date_Select <= 20; "20 days ago"; Date_Select <= 31; "30 days ago"; "> 30 days" )
When you use SWITCH you need to have an expression and then based on that expression you return the several result based on the comparision of values.
In the first case what I do is if the calculated variable return a value, in other words, if the calculation is TRUE then you categorize it by seeing if the variable is within the ranges selected, in other case go to the > 30 days.
If you see the one I wrote above I simplified it by removing the maximum value on each group and the first expression is if it's greater than 0 it will return the > 30 days
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |