DAX Measure Assistance

[Anonymous]

I am looking to create a measure that will return the min value of a filtered group.

The group currently is a support table and has no direct relationships with any of the tables.Relationships

 The measure to obtain the grouping is as follows - 

Total Reading Grouped by Site Size =
VAR RankingDim = VALUES ( Sites1[Site ID] )
RETURN
CALCULATE ( [Total Reading],
FILTER ( RankingDim,
COUNTROWS (
FILTER ( Size_Groupings,
IF ( NOT ( ISBLANK ( [Site Size] ) ),
RANKX ( ALL ( Sites1[Site ID] ), [Site Size],, DESC ) > Size_Groupings[Min]
&&

RANKX ( ALL ( Sites1[Site ID] ), [Site Size],, DESC ) <= Size_Groupings[Max],
BLANK () ) ) ) > 0 ) )

I would like to be able to use a slicer to select which group I am interested in and for the measure to display the min size in that particular group and the total reading for that site.

Desired Result: Result

 The text boxes to the right of the slicer selection shows an example of the ask, and would change when the Group selected changed. I am not likely to use the text box to display the result, more likely to use the Card visual.

Link to PBIX file and Excel file - https://1drv.ms/u/s!ApGb04aQrU58i3Bj9ZntcmIHaVQ8?e=gwAs1S

Thanks in advance

[PaulDBrown · Accepted solution]

@Anonymous 

See if this works:

Min Site size by Group =
MINX (
    SUMMARIZE (
        FILTER (
            ALL ( Sites1[Site ID] ),
            NOT ( ISBLANK ( [Total Reading Grouped by Site Size] ) )
        ),
        Sites1[Site ID],
        "size", [Site Size]
    ),
    [size]
)

Min Site ID by Group =
CALCULATE (
    MAX ( Sites1[Site ID] ),
    FILTER (
        ALLSELECTED ( Sites1[Site ID] ),
        [Min Site size by Group] = [Site Size]
    )
)

Total reading for Min Site ID by Group =
CALCULATE (
    [Total Reading],
    FILTER (
        ALLSELECTED ( Sites1[Site ID] ),
        [Min Site size by Group] = [Site Size]
    )
)

Min Site ID Text =
"Site ID " & [Min Site ID by Group] & " at "
    & FORMAT ( [Min Site size by Group], "Standard" ) & " sqft is the smallest store in this group with a total reading  of "
    & FORMAT ( [Total reading for Min Site ID by Group], "standard" )

I've included the sames calculations to obtain the max site values and text in the attached PBIX file:

[PaulDBrown · Accepted solution]

@Anonymous 

See if this works:

Min Site size by Group =
MINX (
    SUMMARIZE (
        FILTER (
            ALL ( Sites1[Site ID] ),
            NOT ( ISBLANK ( [Total Reading Grouped by Site Size] ) )
        ),
        Sites1[Site ID],
        "size", [Site Size]
    ),
    [size]
)

Min Site ID by Group =
CALCULATE (
    MAX ( Sites1[Site ID] ),
    FILTER (
        ALLSELECTED ( Sites1[Site ID] ),
        [Min Site size by Group] = [Site Size]
    )
)

Total reading for Min Site ID by Group =
CALCULATE (
    [Total Reading],
    FILTER (
        ALLSELECTED ( Sites1[Site ID] ),
        [Min Site size by Group] = [Site Size]
    )
)

Min Site ID Text =
"Site ID " & [Min Site ID by Group] & " at "
    & FORMAT ( [Min Site size by Group], "Standard" ) & " sqft is the smallest store in this group with a total reading  of "
    & FORMAT ( [Total reading for Min Site ID by Group], "standard" )

I've included the sames calculations to obtain the max site values and text in the attached PBIX file:

[Anonymous]

PaulDBrown, thanks for your help in this.

Can I ask is there a way to only return a single value for the measure - 
Total reading for Min Site ID by Group =
CALCULATE (
[Total Reading],
FILTER (
ALLSELECTED ( Sites1[Site ID] ),
[Min Site size by Group] = [Site Size]
)
)

When there are two sites with identical sizes, the [Total Reading] returns the values summed.

[PaulDBrown · Accepted solution]

@Anonymous 

try:

MINX(FILTER (
ALLSELECTED ( Sites1[Site ID] ),
[Min Site size by Group] = [Site Size]
),

[Total reading])

[Anonymous]

Hi VijayP. Sorry I maybe didn't explain properly. The measure I posted shows how I calculate the groups and not the measure to bring back the min values. 

I don't have an example of what I have attempted so far to obtain the single min value for the selected group.

[VijayP]

@Anonymous 

RANKX ( ALL ( Sites1[Site ID] ), [Site Size],, DESC ) > Size_Groupings[Min]
&&

RANKX ( ALL ( Sites1[Site ID] ), [Site Size],, DESC ) <= Size_Groupings[Max]

Change > to >=

and 

Change <= to <  and let me know whether you get desired result