Forum Discussion

hackfifi's avatar
hackfifi
Helper V
5 years ago
Solved

Return Value from Table based on Measure Condition

Hi - I am trying to return the column value "Month" based on Measure (% Complete)

In the example below, i would like to return the "MONTH" when a "Project" reaches 10% and 90%

Hence in the example below, For Project A i would like to return the value Month = 2 (For 10%) & Month = 5 (For 90%)

 

I tried the below measure and it did not work

10% Month =
CALCULATE(MIN('Data'[Period]),FILTER('Data', [% Complete]>=0.1))

90% Month =
CALCULATE(MIN('Data'[Period]),FILTER('Data', [% Complete]>=0.9))

 

Cheers

 

TABLE : DATA
Project% Complete     Month
A5%1
A12%2
A30%3
A80%4
A90%5
A100%6
B8%1
B9%2
B15%3
B25%4
B55%5
B75%6
B85%7
B90%8
B100%9
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    I did think it was a column.  Here is a different expresssion that works with a measure.  Just replace 0.1 with 0.9 for the other measure.

     

    10 Pct Month =
    VAR summary =
        ADDCOLUMNS (
            SUMMARIZE (
                Data,
                Data[Project],
                Data[Location],
                Data[Month]
            ),
            "@PctComplete", [% Complete Measure]
        )
    RETURN
        MINX (
            FILTER (
                summary,
                [@PctComplete] >= 0.1
            ),
            Data[Month]
        )

     

    Regards,

    Pat

     

5 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

     

    [10% Month] =
// For the other measure,
// just change the __perc
var __perc = .1
return
if( hasonefilter( T[Project] ),
    calculate(
        minx(    
            filter(
                T,
                T[% Complete] >= __perc
            ),
            T[Month]
        ),
        allexcept( T, T[Project] )
    )
)

     

    • hackfifi's avatar
      hackfifi
      Helper V
      5 years ago

      Apologies daxer-almighty  - i did not get the right result, but probably because my DATA TABLE was incorrect. Sorrt about that... I have another column "Location"

       

      So For Project A Location 1, the Result should be 2 for 10%

      So For Project A Location 1, the Result should be 4 for 90%
      So For Project A Location 2, the Result should be 3 for 10%

      So For Project A Location 2, the Result should be 5 for 90%

       

      TABLE : DATA
      ProjectLocation% CompleteMonth
      A15%1
      A112%2
      A150%3
      A190%4
      A1100%5
      A210%3
      A280%4
      A290%5
      A2100%7
      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        5 years ago

        This measure expression has both expression you'll need as two variable, and concatenates them together in the Return.  If you need them separate, you can just Return either the month10 or month90 variables.

         

        10 to 90 Months =
        VAR month10 =
            CALCULATE (
                MIN ( Data[Month] ),
                Data[% Complete] >= 0.1
            )
        VAR month90 =
            CALCULATE (
                MIN ( Data[Month] ),
                Data[% Complete] >= 0.9
            )
        RETURN
            "10% Month - " & month10 & "  90% Month - " & month90

         

        Regards,

        Pat