How to find the Min in PowerBI

[Anonymous]

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. 

  

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

[mwegener]

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.

[Anonymous]

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

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

[mwegener · 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.

[Anonymous]

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?

[mwegener]

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.

[Anonymous]

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.

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]

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