Forum Discussion
Power BI Desktop Summarized Table with custom function
- 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
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
Owen,
That was EXACTLY what I wanted to do. I tried to assign variables in my example but as you know I was unable to but thankfully you put me in the right direction and solution - thank you!
~ Brian
Note:
The only thing that was needed was to tweak the Host_Totals Visual table ..... The summarization did not come up and I had to manually assign to SUM for Low, Medium, High, Critical, Total and Avg for VPH
I included image this in case someone uses this solution and notices no totals wew populated: