Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Creating a table from another table with filters

HI All,

 

I am a not a pro Power Bi user  but have the basics down and have created many reports.  Below you will see a source table - GoldList.  I am trying to create a new table that is a summary of the Customers and their Upgrade status.  If all devices for the customerr are Complete or Out Of Scope, they are considered to be Completed.  Any other status will make them Not Done.

 

Thank you and appreciate any help!

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    You can try code as below to create a calculated table.

    GoldListSummary =
SUMMARIZE (
    GoldList,
    GoldList[Customer],
    "Total Devices", CALCULATE ( COUNT ( GoldList[Device Name] ) ),
    "# Remaining",
        CALCULATE (
            COUNT ( GoldList[Device Name] ),
            NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
        ) + 0,
    "Done?",
        IF (
            CALCULATE (
                COUNT ( GoldList[Device Name] ),
                NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
            ) + 0 = 0,
            "Completed",
            "Not Done"
        )
)

    You can also create a table visual with measures.

    Total Devices = CALCULATE(COUNT(GoldList[Customer]))
    # Remaining = 
        CALCULATE (
            COUNT ( GoldList[Device Name] ),
            NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
        ) + 0
    Done? = 
IF (
            [# Remaining] = 0,
            "Completed",
            "Not Done"
        )

    Result is as below.

     

    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.

     

2 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Anonymous why do you need a new table for this?

     

    Wouldn't creating appropriate measures be better?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    You can try code as below to create a calculated table.

    GoldListSummary =
SUMMARIZE (
    GoldList,
    GoldList[Customer],
    "Total Devices", CALCULATE ( COUNT ( GoldList[Device Name] ) ),
    "# Remaining",
        CALCULATE (
            COUNT ( GoldList[Device Name] ),
            NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
        ) + 0,
    "Done?",
        IF (
            CALCULATE (
                COUNT ( GoldList[Device Name] ),
                NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
            ) + 0 = 0,
            "Completed",
            "Not Done"
        )
)

    You can also create a table visual with measures.

    Total Devices = CALCULATE(COUNT(GoldList[Customer]))
    # Remaining = 
        CALCULATE (
            COUNT ( GoldList[Device Name] ),
            NOT ( GoldList[Upgrade Status] IN { "Completed", "Out of Scope" } )
        ) + 0
    Done? = 
IF (
            [# Remaining] = 0,
            "Completed",
            "Not Done"
        )

    Result is as below.

     

    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.