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
Anonymous
Not applicable

How to find the Min in PowerBI

I have an email column, status column and a sequence column.

I would like to get the Min Sequence Number and show that status group with Dax. 

 

PBI picture.PNG  

 

 

The result I am looking for in this example is to only show Completed and not In Progess

because Completed has the Min Sequence Number of 1

 

The Sequence Number is dynamic and therefore I can not simply say equal to 1 and this may be a different number

 

 

In this second example I want to show In Progress and not Disqualified

 

PBI pic2.PNG

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

it depends on which filters should be considered.

Check this.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

7 REPLIES 7

Hi @Anonymous ,

 

check this out

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Thank you for your help. 

This seems to be working as long as the Min Email Seq is 1.

Unfortunately this number is dynamic meaning for some folks their Min is 3 or another number.

Below I was hoping to see a 3 for the .Test 3 column as this is the Min for that person

 

Power BI pic 3.PNG

 

For clarity, the Min Email Seq is based on the a Date and STATUS. 

As seen below if I choose all the dates for the same email then I am looking for Completed 1 and In Progress 2

However, if I choose the date of 3/27 I am looking for In Progress 2

 

 

Power BI pic 3.PNG

Hi @Anonymous ,

 

it depends on which filters should be considered.

Check this.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Thank you.

Your solution appears to be working. I did want to ask, though. 

I noticed that if I move my status to a column from a row, a value somehow gets added but the Total remains the same. 

Is this due to the AllSelected functions being used?

 

PBI pic 3.PNG

 

Hi @Anonymous ,

 

I do not think so.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Thank you for your help on this.

 

Unfortunately I am still running into a challenge. 

The logic before was correct until looking at the whole year. At this point I am still looking for the Min_Lead_Email

If the date range changes I am looking for the Min_Lead_Email for that date range (if that makes since).

 

In the Ex below using the recent pbix file. If I have a date slicer and I choose January then I am looking for the Min_Lead_Email,

However, if I choose the whole year, I am still looking for the Min_Lead_Email(for the year).

 

As seen below if I choose the year of 2020 and looking at the email Test@test.de,

then I want to only count Jan cause that has the min amoung the months

 

The goal is to count .Test3 but using the Min_Lead_Email and a desired dynamic date range.

 

 

 

PBI Sample.PNG
 
 
 
 
 
 
 
 
 
 
The logic below works for a specific month but if the date range is expanded for the whole year it still looks at that specific month
Instead of dynamicly changing for the whole year. 
 
.Test3 =
IF (
MIN ( 'Table2'[.Min_Lead_Email] )
= CALCULATE (
MIN ( 'Table2'[.Min_Lead_Email] ),
ALLSELECTED ( Table2[TASK_STATUS_GROUP] ),
ALLSELECTED ( Table2[FROM_FILL_DATE] ),
ALLSELECTED ( Table2[FROM FILL DATE (MONTH)] ),
ALLSELECTED ( Table2[FROM FILL DATE (YEAR)] )
),
MIN ( 'Table2'[.Min_Lead_Email] ),
BLANK ()
)
 
amitchandak
Super User
Super User

Sort on sequence and display on one record.

 

Or

Rank and filter on one . Refer : https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors