Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Result based on the measures

Hi All,

IF((COUNTIF(AA10:AM10,"At Risk")>0), "At Risk", IF((COUNTIF(AA10:AM10,"Delayed")>0), "Delayed", IF((COUNTIF(AA10:AM10,"On Track")>0), "On Track", IF((COUNTIF(AA10:AM10,"Complete")>0), "Complete", IF((COUNTIF(AA10:AM10,"Not Started")>0), "Not Started", "Error"))))) 

This is excel formula I to replicate same in PowerBI.

In excel they have se;ected the cell (AA1:AM10) in powerbi I have a measure created for the sam.

Please help me with this formula.

 

Thanks in Advance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Due to we can build measures in columns in Power BI, if your Data model like below, you need to select All columns except Row column and do unpivot to transform the table2 like table and table3.

    Update the measure:

    Measure = 
VAR _1 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "At Risk" )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Delayed" )
    )
VAR _3 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "On Track" )
    )
VAR _4 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Complete" )
    )
VAR _5 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Not Started" )
    )
RETURN
    IF (
        _1 > 0,
        "At Risk",
        IF (
            _2 > 0,
            "Delayed",
            IF (
                _3 > 0,
                "On Track",
                IF ( _4 > 0, "Complete", IF ( _5 > 0, "Not Started", "Error" ) )
            )
        )
    )

    Build color measures like M.Color.

    M.Color = SWITCH([Measure],"On Track",1,2)

     Build Matrix visual and add Color measures into conditional formatting in Format(Format by rule and add the color measure).

    Result:

    You can download the pbix file from this link: Result based on the measures

     

    Best Regards,

    Rico Zhou

     

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    I tried your Measure in Excel, I think you want to output specific result by the counting of specific counting of the words.

    However in Power Bi, the measure will work in a column without in a row.

    Due to I don't know your data model, so I build a sample table in Excel AA9:AM10.

    Get data in Power Bi, and I think you need to do Transform in Power Query Editor to use  Transpose.

    Sample:

    New Table, and column2 is AA10:AM10 in Excel.

    Measure:

    Measure = 
VAR _1 =
    CALCULATE (
        COUNT ( Sheet1[Column2] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Column2] = "At Risk" )
    )
VAR _2 =
    CALCULATE (
        COUNT ( Sheet1[Column2] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Column2] = "Delayed" )
    )
VAR _3 =
    CALCULATE (
        COUNT ( Sheet1[Column2] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Column2] = "On Track" )
    )
VAR _4 =
    CALCULATE (
        COUNT ( Sheet1[Column2] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Column2] = "Complete" )
    )
VAR _5 =
    CALCULATE (
        COUNT ( Sheet1[Column2] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Column2] = "Not Started" )
    )
RETURN
    IF (
        _1 > 0,
        "At Risk",
        IF (
            _2 > 0,
            "Delayed",
            IF (
                _3 > 0,
                "On Track",
                IF ( _4 > 0, "Complete", IF ( _5 > 0, "Not Started", "Error" ) )
            )
        )
    )

    Result:

    You can download the pbix file from this link: Result based on the measures

     

    Best Regards,

    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Anonymous  Hi,

      Thanks for ypur rpl !

      I will share my data sample so that will give you clear idea for my requirment.

       

      1st pic show what I need to achieve and 2nd Pic show the formula which I need to achieve I powerbi.

      For each status I have created measures so now based on that average need to do achieve"OVERALL" column 

       

       

      Below is my power bi report.   Green clr "On Track" this cards are measures.

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        Due to we can build measures in columns in Power BI, if your Data model like below, you need to select All columns except Row column and do unpivot to transform the table2 like table and table3.

        Update the measure:

        Measure = 
VAR _1 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "At Risk" )
    )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Delayed" )
    )
VAR _3 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "On Track" )
    )
VAR _4 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Complete" )
    )
VAR _5 =
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ROW]=MAX('Table (2)'[ROW])&&'Table'[Value] = "Not Started" )
    )
RETURN
    IF (
        _1 > 0,
        "At Risk",
        IF (
            _2 > 0,
            "Delayed",
            IF (
                _3 > 0,
                "On Track",
                IF ( _4 > 0, "Complete", IF ( _5 > 0, "Not Started", "Error" ) )
            )
        )
    )

        Build color measures like M.Color.

        M.Color = SWITCH([Measure],"On Track",1,2)

         Build Matrix visual and add Color measures into conditional formatting in Format(Format by rule and add the color measure).

        Result:

        You can download the pbix file from this link: Result based on the measures

         

        Best Regards,

        Rico Zhou

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi Anonymous - will you be to share any sample data for the below problem - also can you share your expected result. Thanks!