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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Niiru1
Helper V
Helper V

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
Super User

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
Super User

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors