Forum Discussion

Jeremy19's avatar
Jeremy19
Icon for Helper III rankHelper III
5 years ago
Solved

Min/max/AVG by filters data

Hello,   I have two tables "DB"  Name Var1 Date Condition A 1000 43831 C1 A 1100 43832 C1 A 1362 43831 C2 A 740 43832 C2 B 1237 43831 C1 B 1354 43832 C2 ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi Jeremy19 ,

     

    Is this what you want? Or you want a "Player" slicer?

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Please try this measure expression to get your desired result.  This one is the min, but you can adapt it for max and avg as needed.  Note this assumes you have no relationship between the tables and the Name column from the Positions table is used in the slicer.  Note I got a min of 922 since name B is in same position.  Replace Var1 and Positions with your actual table names.

 

Min Var1 =
VAR vThisName =
    VALUES ( Positions[Name] )
VAR vNamesSamePosition =
    CALCULATETABLE (
        VALUES ( Positions[Name] ),
        VALUES ( Positions[Position] ),
        ALL ( Positions[Name] ),
        NOT ( Positions[Name]
            IN vThisName )
    )
RETURN
    CALCULATE (
        MIN ( Var1[Var1] ),
        TREATAS (
            vNamesSamePosition,
            Var1[Name]
        ),
        Var1[Condition] = "C1"
    )

 

Pat

 

  • Jeremy19's avatar
    Jeremy19
    Icon for Helper III rankHelper III
    5 years ago

    Thank you very much, this is not far from what I need. No need for the option to ignore the player but I just need to remove the NOT. On the other hand the problem again is that I need the MIN but among the MAX of each player in the same position (1100,1237 or 1086). While here I have the min on all the values of the players of the same position.