cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
faisalt
Frequent Visitor

How to find Min and Max within the group in power bi

I am trying to find dax formula to show in the card to display the season range between the year and week.I want get the min week in the min year and max week and max year :

I have the below table:

 

 

table_refernce.png

I want to show the card like below:

"Between 2020-33 and 2021-1"

But the below formula shows as : "Between 2020-1 and 2021-52"

 

Can any one please help me to find the dax to show the correct card ..My current formula is like below:

 

Min_Season = CALCULATE(Min('Year-Week_Table'[Year]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1)) & "-" & CALCULATE(Min('Year-Week_Table'[Week]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1))&" and " & CALCULATE(MAX('Year-Week_Table'[Year]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1)) & "-" & CALCULATE(max('Year-Week_Table'[Week]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1))

 

 

 

 

I want to show the card like below: "Between 2020-33 and 2021-1"

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @faisalt 

Download sample PBIX with data, measures and visuals.

Based on your data the MIN year is 2019 and the MIN week in 2019 is 52.  Likewise the MAX year is 2021 and the MAX week in that year is 1.

Therefore shouldn't your message be Between 2019-52 and 2021-1 ?

The MIN Year-Week measure is

Min Year-Week = MIN('year-week'[Year]) & "-" & CALCULATE( MIN('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MIN('year-week'[Year])))

 

The MAX Year-Week measure is 

Max Year-Week = MAX('year-week'[Year]) & "-" & CALCULATE( MAX('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MAX('year-week'[Year])))

 

Combine the two in your card

Card Msg = "Between " & [Min Year-Week] & " and " & [Max Year-Week]

 

To give

 min-year-wk.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

3 REPLIES 3
PhilipTreacy
Super User III
Super User III

Hi @faisalt 

Download sample PBIX with data, measures and visuals.

Based on your data the MIN year is 2019 and the MIN week in 2019 is 52.  Likewise the MAX year is 2021 and the MAX week in that year is 1.

Therefore shouldn't your message be Between 2019-52 and 2021-1 ?

The MIN Year-Week measure is

Min Year-Week = MIN('year-week'[Year]) & "-" & CALCULATE( MIN('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MIN('year-week'[Year])))

 

The MAX Year-Week measure is 

Max Year-Week = MAX('year-week'[Year]) & "-" & CALCULATE( MAX('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MAX('year-week'[Year])))

 

Combine the two in your card

Card Msg = "Between " & [Min Year-Week] & " and " & [Max Year-Week]

 

To give

 min-year-wk.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @faisalt ,

 

Sorry, your description is difficult to make people clear where the problem lies.

You could see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

You could directly describe the calculation logic instead of the formula you use, and share your sample .pbix.

Please remove any sensitive data before uploading.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User IV
Super User IV

@faisalt , Create a column like

 

Year week =

var _yrw = ([year]*100 + [week] ) &""

return

left(_yrw,4) & "-" & right(_yrw,2)

 

Now min and max of this column is easy

a new meausre

measure = "Between " & min([Year week]) &" and " & max(Table[Year week])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors