Creating a table from another table with filters

[NathanKatsu]

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!

[Anonymous · Accepted solution]

Hi @NathanKatsu ,

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.

[littlemojopuppy]

@NathanKatsu why do you need a new table for this?

Wouldn't creating appropriate measures be better?