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.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

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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])

5 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

        Anonymous 

        try:

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

        [Total reading])