Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mcinnisbr
Advocate I
Advocate I

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

mcinnisbr_0-1634942476725.png

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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.

View solution in original post

1 REPLY 1
v-robertq-msft
Community Support
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.