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

No Comments

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

)

No Comments

Install XPEnology

Posted by allenkwc in Technology, VMWare on August 9, 2015

Download DSM and XPEnoboot (Note the version must be matched!)

http://xpenology.me/downloads/

http://www.xpenology.nl/vmware-esxi-installatie/

http://www.xpenology.nl/installation-dsm-5-1/

No Comments

Creating a new datastore fails with the error: An error occurred during host configuration

Posted by allenkwc in VMWare on June 8, 2015

I have ESXi 5, I was adding a 500 gigs IDE drive and I got the error.
I just did a “ls /vmfs/devices/disks/” from the shell to identify what was the vml I had to use.
To find the vml I just ran the following command “partedUtil get /vmfs/devices/disks/VMLID” and I got an error with the VMLID corresponding to the new drive.
After that, I just ran your “dd if=/dev/zero of=”/vmfs/devices/disks/VMLID” bs=512 count=34 conv=notrunc” and I was able to add the hard drive.

No Comments

Single Sign on in Firefox

Posted by allenkwc in Other on January 13, 2015

In the address bar of Firefox, type about:config to display the list of current configuration options.
In the Filter field, type negotiate to restrict the list of options.
Double-click the network.negotiate-auth.trusted-uris entry to display the Enter string value dialog box.
Enter the name of the domain against which you want to authenticate, for example, .example.com.
Repeat the above procedure for the network.negotiate-auth.delegation-uris entry, using the same domain.