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

     

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

       

      • hackfifi's avatar
        hackfifi
        Helper V
        5 years ago

        mahoneypat  - Thank you for responding.
        I used the below "measure" calculation as per your suggestion, and i get the error message: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

        Kindly note the "% Complete" is a calculated measure; and not part of the table.

         

        10% Months_No =
        VAR month10 =
        CALCULATE (
        MIN ( 'Data'[Period] ),
        [% Complete]>=0.1
        )
        RETURN
        month10