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

     

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
    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      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