CODEWITHSUNDEEP
minpowerpivot
Mark Walmsley
Mark Walmsley

Reputation: 1

Powerpivot - Only Show Minimum Value

Newbie to DAX/PowerPivot and struggling with a specific problem. I have a table

 Location  Category  Distance
 1         A         1.244
 2         A         2.111
 3         B         5.113
 4         C         0.124
 etc

I need to identify the Minimum distance out of the selection and only output for that record. So I'd have

Location  Category  Distance   MinDist
 1         A         1.244
 2         A         0.111     0.111
 3         B         5.113
 4         C         3.124
 etc

I've tried various measures but always end up with simply a repeat of the Distance column....whatever filters I try to apply. Please help.

Upvotes: 0

Views: 139

Answers (1)

Jacob
Jacob

Reputation: 3557

If your table was called 'table1' then this would give you the overall minimum:

=CALCULATE(MIN(Table1[Distance]), ALL(Table1))

Depending on your requirements, you may have to specify columns in the ALL() to reduce how much of the filter is opened up (suggest you research ALL() as it is a very important DAX function).

To return zero (blanks is tricky) for the non matchers you could package it in:

=
 IF (SUM ( Table1[Distance] ) = CALCULATE ( MIN ( Table1[Distance] ), ALL ( Table1 ) ),
          CALCULATE ( MIN ( Table1[Distance] ), ALL ( Table1 ) ),
          0
     )

Upvotes: 1

Related Questions