Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI'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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingClose, 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |