cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niiru1
Helper III
Helper III

Get min and max value in column to display age in years

I have an ages column with ages ranging from  0 -106.

 

Is there a way to run / get a measure like MINX and MAXX dax query to get the result to display "0 - 106 years" 

 

I've tried adding columns but no luck:

MAXX(
FILTER(
SELECTCOLUMNS(
ALLSELECTED('Enhanced line list'),
"id", 'Enhanced line list'[Event ID],
"max", MAXX(ALLSELECTED('Enhanced line list'), 'Enhanced line list'[Age (Years) at time of event])
),
[id] = MAX('Enhanced line list'[Event ID])
),
[max]
)

and: 

Age Range (Years) Min = 
VAR eventid = 'Enhanced line list'[Event ID]
VAR agemin = MINX(FILTER(ALL('Enhanced line list'),'Enhanced line list'[Event ID] = eventid),'Enhanced line list'[Age (Years) at time of event])

RETURN
agemin

 

 

 

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

I'd need to see some data (see links below) to really assist and be sure it works, but something along this line of this @Niiru1 should work:

Age Range (Years) Min =
VAR eventid =
    MAX( 'Enhanced line list'[Event ID] )
VAR agemin =
    MINX(
        FILTER(
            ALL( 'Enhanced line list' ),
            'Enhanced line list'[Event ID] = eventid
        ),
        'Enhanced line list'[Age (Years) at time of event]
    )
VAR agemax =
    MAXX(
        FILTER(
            ALL( 'Enhanced line list' ),
            'Enhanced line list'[Event ID] = eventid
        ),
        'Enhanced line list'[Age (Years) at time of event]
    )
VAR varTextString =
    "Age range: "
        & FORMAT(
            agemin,
            "#"
        ) & "-"
        & FORMAT(
            agemax,
            "#"
        )
RETURN
    varTextString

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User III
Super User III

I'd need to see some data (see links below) to really assist and be sure it works, but something along this line of this @Niiru1 should work:

Age Range (Years) Min =
VAR eventid =
    MAX( 'Enhanced line list'[Event ID] )
VAR agemin =
    MINX(
        FILTER(
            ALL( 'Enhanced line list' ),
            'Enhanced line list'[Event ID] = eventid
        ),
        'Enhanced line list'[Age (Years) at time of event]
    )
VAR agemax =
    MAXX(
        FILTER(
            ALL( 'Enhanced line list' ),
            'Enhanced line list'[Event ID] = eventid
        ),
        'Enhanced line list'[Age (Years) at time of event]
    )
VAR varTextString =
    "Age range: "
        & FORMAT(
            agemin,
            "#"
        ) & "-"
        & FORMAT(
            agemax,
            "#"
        )
RETURN
    varTextString

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Close, please find link attached. I should probably have noted that there was an Unknown value in my age column but I was trying to keep my question simple

 

https://www.dropbox.com/s/2f5okxkdogo2olu/Age%20Range.pbix?dl=0

edhans
Super User III
Super User III

Not sure exactly what you are looking for @Niiru1. If you want this in a card, then you don't need to know the event ID. This will work in a card. I had to filter out the blank row. That shouldn't be in the data anyway.

Age Range (Years) No ID =
VAR agemin =
    MINX(
        FILTER(
            ALL( 'COVID-19 Enhanced line list'[Age (Years) at time of event] ),
            'COVID-19 Enhanced line list'[Age (Years) at time of event]
                <> BLANK()
        ),
        'COVID-19 Enhanced line list'[Age (Years) at time of event]
    )
VAR agemax =
    MAXX(
        ALL( 'COVID-19 Enhanced line list'[Age (Years) at time of event] ),
        'COVID-19 Enhanced line list'[Age (Years) at time of event]
    )
VAR varTextString =
    "Age range: "
        & FORMAT(
            agemin,
            "#"
        ) & "-"
        & FORMAT(
            agemax,
            "#"
        )
RETURN
    varTextString

The measure I provided earlier though works great in a table - again, I'd get rid of that blank value. I did add an IF() function to the end though to prevent it from showing up in a total row.

Age Range (Years) Min = 
VAR eventid =
    MAX( 'COVID-19 Enhanced line list'[Event ID] )
VAR agemin =
    MINX(
        FILTER(
            ALL( 'COVID-19 Enhanced line list' ),
            'COVID-19 Enhanced line list'[Event ID] = eventid
        ),
        'COVID-19 Enhanced line list'[Age (Years) at time of event]
    )
VAR agemax =
    MAXX(
        FILTER(
            ALL( 'COVID-19 Enhanced line list' ),
            'COVID-19 Enhanced line list'[Event ID] = eventid
        ),
        'COVID-19 Enhanced line list'[Age (Years) at time of event]
    )
VAR varTextString =
    "Age range: "
        & FORMAT(
            agemin,
            "#"
        ) & "-"
        & FORMAT(
            agemax,
            "#"
        )
RETURN
    IF(
        HASONEVALUE('COVID-19 Enhanced line list'[Event ID]),
        varTextString,
        BLANK()
    )

Both measures return this - one in the card with no filter on EventID, and one in the table that does.

edhans_0-1605142408144.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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