Forum Discussion

bslintx's avatar
bslintx
Frequent Visitor
1 year ago
Solved

Power BI Desktop Summarized Table with custom function

Hi, I am trying to dynamically create a summarized query/table that groups/summarizes a "vulnerabilities" table. I have it to where it groups ip addresses/organizations.... and breaks out/summarize...
  • OwenAuger's avatar
    1 year ago

    Hi bslintx 

    I would recommend rewriting like this:

     

    Host_Totals =
    GENERATE (
        SUMMARIZE (
            Vulnerabilities,
            Vulnerabilities[Organization],
            Vulnerabilities[IP Address]
        ),
        VAR Low =
            CALCULATE ( COUNTROWS ( Vulnerabilities ), 'Vulnerabilities'[Severity] = "Low" ) + 0
        VAR Medium =
            CALCULATE (
                COUNTROWS ( Vulnerabilities ),
                'Vulnerabilities'[Severity] = "Medium"
            ) + 0
        VAR High =
            CALCULATE (
                COUNTROWS ( Vulnerabilities ),
                'Vulnerabilities'[Severity] = "High"
            ) + 0
        VAR Critical =
            CALCULATE (
                COUNTROWS ( Vulnerabilities ),
                'Vulnerabilities'[Severity] = "Critical"
            ) + 0
        VAR Total =
            CALCULATE ( COUNTROWS ( Vulnerabilities ) )
        VAR VPH = ( ( Low ) + ( Medium * 4 ) + ( High * 10 ) + ( Critical * 10 ) ) / 15
        RETURN
            ROW (
                "Low", Low,
                "Medium", Medium,
                "High", High,
                "Critical", Critical,
                "Total", Total,
                "VPH", VPH
            )
    )

     

    By using GENERATE with ROW in this way, it is possible to declare variables within the second argument of GENERATE which can reference each other, then return the required column values within ROW.

     

    See this article for a discussion of this method:

    https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/

     

    This article discusses why extension columns should not be added with SUMMARIZE itself:

    https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

     

    Does the above work for you?

     

    Regards