Archive for September, 2015
Splitting An Attribute’s details into columns in MicroStrategy
Posted by allenkwc in Data Analysis on September 24, 2015
Refresh the powerpivot data
Posted by allenkwc in Data Analysis on September 22, 2015
Excel 2013 only.
“Refresh the powerpivot data”
$ReportWorkbook = $excel.Workbooks.Open($ReportFilePath)
$ReportWorkbook.Connections | %{
#If PowerPivot Model (Type=7)
#if($_.Type -eq “7”)
#{
” – Refreshing PowerPivot Model: ” + $_.Name
if($_ -ne $null){
$_.Refresh()
}
#}
}
$ReportWorkbook.Save()
$ReportWorkbook.Close()
DAX: Count Distinct in Excel
Posted by allenkwc in Data Analysis, Technology on September 22, 2015
Excel 2010
Count Distinct in Excel 2010, use PowerPivot
1. In the PowerPivot, right click the table name, press “Add Measure”
2. Enter the formula with “CountRows” and “Distinct”, below is an example.
=countrows(distinct(<column>))
However, it count “BLANK” value as 1. In order to prevent that, use below formula
=CALCULATE(countrows(distinct(<column>)),NOT(ISBLANK(<column>)))
Excel 2013
=DISTINCTCOUNT(<column>)
However, it count “BLANK” value as 1. In order to prevent that, use below formula
= CALCULATE (
DISTINCTCOUNT(<column>),
NOT(ISBLANK(<column>))
)