Dynamic grouping count from a measure

[mcinnisbr]

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):

[v-robertq-msft · Accepted solution]

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.