Archive for September, 2015

Splitting An Attribute’s details into columns in MicroStrategy

If they are running the reports in Web, there is button under the Data menu titled Toggle Attribute Forms.  I think that might accomplish what you are after.  Not sure if there is a setting for this in Desktop at the grid level though.

No Comments

Refresh the powerpivot data

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()

No Comments

DAX: Count Distinct in Excel

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>))

)

No Comments