Forum Discussion
Advocate IDynamic grouping count from a measure
Hi,
I have a dynamic switch statement that (so far) currently identifies the column as I would like. and based on a duedate column. (not sure if it's as elegant) What I would like to do (maybe it's not possible using a measure per say) is dynamically count the total for each flag/or grouping (past due, closed, due tomorrow, due today, no due date...etc.) so that the user can see a count in a card format? I suppose the better method is to just tag my data the same way in a calculated column, which would make it easier to count i suppose. Anyway, just wondering.
Status =
VAR DaysFromExpiry =
DATEDIFF (
TODAY (),
MAX('table'[SubmissionDueDate] ),DAY
)
VAR Switch =
SWITCH (
TRUE (),
vDaysFromExpiry < 0, "🚩 Past Due",
vDaysFromExpiry = 0, "🕗 Due Today!",
vDaysFromExpiry <= 1, "🕗 Due Tomorrow!",
"📆 Future Date"
)
RETURN
SWITCH(TRUE(),
MAX('table'[DateClosed]) <> BLANK (), "✔️ Closed",
MAX('table'[SubmissionDueDate]) = BLANK(), "🔔 No Due Date",
vSwitch)
Essentially, I pull that measure over to a table, and i get something like this (and what i want to do is dynamincally count these groups out side of the table view):
Hi,
As far as I’m concerned, if you want to create a calculated column to get the same result as the measure [Status], you can just simply remove the MAX() from the table fields you used, you can create a calculated column like this:
Status = VAR DaysFromExpiry = DATEDIFF ( TODAY (), 'table'[SubmissionDueDate] ,DAY ) VAR Switch = SWITCH ( TRUE (), vDaysFromExpiry < 0, "Past Due", vDaysFromExpiry = 0, "Due Today!", vDaysFromExpiry <= 1, "Due Tomorrow!", "Future Date" ) RETURN SWITCH(TRUE(), 'table'[DateClosed] <> BLANK (), "Closed", 'table'[SubmissionDueDate] = BLANK(), "No Due Date", vSwitch)For more advice about this trick, please refer to this link:
https://forum.enterprisedna.co/t/convert-a-measure-to-a-calculated-column/4743
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-robertq-msft
Community Support
Hi,
As far as I’m concerned, if you want to create a calculated column to get the same result as the measure [Status], you can just simply remove the MAX() from the table fields you used, you can create a calculated column like this:
Status = VAR DaysFromExpiry = DATEDIFF ( TODAY (), 'table'[SubmissionDueDate] ,DAY ) VAR Switch = SWITCH ( TRUE (), vDaysFromExpiry < 0, "Past Due", vDaysFromExpiry = 0, "Due Today!", vDaysFromExpiry <= 1, "Due Tomorrow!", "Future Date" ) RETURN SWITCH(TRUE(), 'table'[DateClosed] <> BLANK (), "Closed", 'table'[SubmissionDueDate] = BLANK(), "No Due Date", vSwitch)For more advice about this trick, please refer to this link:
https://forum.enterprisedna.co/t/convert-a-measure-to-a-calculated-column/4743
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.