Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Measure Assistance

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.    The me...
  • PaulDBrown's avatar
    5 years ago

    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's avatar
    PaulDBrown
    5 years ago

    Anonymous 

    try:

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

    [Total reading])