RANKX in DAX: How to include Your Company even when they are not in top 5

My customer wanted to see the top 5 providers of a service in a Power BI Report. I added a RANKX calculation to the model.

RANK =rankx(all(Sales[Organization Name]),sum(Sales[Volume]),,DESC,Skip)


Here any filtering is removed to get all sales Organizations, then the sum of the Volume is used to rank the Organizations in descending order.  The Skip will skip ranks on ties.  So if two Organizations are ranked 3rd, then the next organization will be ranked 5th (skipping 4th).

Organization Rank Sum of Volume
Org A 1 10,000,000
Org B 2 9,000,000
Org C 3 8,000,000
Org D 3 8,000,000
Org E 5 6,000,000
Org F 6 5,000,000
Org G 7 4,000,000
Org H 8 3,000,000
Org I 9 2,000,000
Org J 10 1,000,000
Org K 11 500,000
Org X 12 200,000


In Power BI we can add a filter to the display to show just the top 5 organizations.


The filter is a shown below.


Including Our Company

My customer liked this but they had one more request.  The wanted to always see our company so they could compare.  Our company is Org X.  In the example we are ranked 11th in this market.  So how do you include us?

I came up with the following solution.

RankwithOrgX:=SWITCH(FIRSTNONBLANK(Sales[Organization Name],sum(Sales[Volume])),”Org X”,0,[Rank])

I use the FIRSTNONBLANK function to find our organization “Org X”.   Using the switch function, I place a zero value for our Organization.  For all other Organizations I use the value from the Rank calculation.

Organization Rank RankwithOrgX Volume
Org A 1 1 10,000,000
Org B 2 2 9,000,000
Org C 3 3 8,000,000
Org D 3 3 8,000,000
Org E 5 5 6,000,000
Org F 6 6 5,000,000
Org G 7 7 4,000,000
Org H 8 8 3,000,000
Org I 9 9 2,000,000
Org J 10 10 1,000,000
Org K 11 11 500,000
Org X 12 0 200,000

Org X shows as 0 under RankwithOrgX.

Now if we filter on RankwithOrgX less than or equal to 5. (remove our previous Rank filter)


Now our chart will include Our Company no matter its Rank.





SSIS Connection Not Found Error but Package still runs

I had a package that I copied and updated to a new connection.  The package runs successfully.  But when I open it it shows the following error.

Error loading Master DatawarehouseETL.dtsx:  The connection “{42E5886D-F897-4366-9794-02AF8E32198D}” is not found.

Everything runs.  So what is the problem?

The problem is I have steps that are disabled.  SSIS still checks the connections even though the step is disabled.  The package runs since the step does not run.  I fixed the connection and the error goes away.


Quickest way to find the SQL Server Templates Location?

If you use SQL Server Templates,  you probable will want to backup up your scripts or copy the files to a new computer so you don’t lose all of your work.  The problem is the location of the files can vary depending on your version of SQL Server and no one can memorize the folder location.   However there is a quick way.



Right click on SQL Server Templates folder in the Template BrowserSQL Server Templates

Select Search

Search Templates

Type “a” into the search window


Notice the search goes out to the location of the templates. In my case it is

C:\Users\rileyt\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\SQL

Now I can go out to the folder,copy and backup my templates.

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”

I ran into this message when attempting to delete a SQL Job.  Somehow a  subplan was orphaned and not allowing me to delete the job.

First I got the Job_id by right clicking on the job and scripting the drop.


The script will contain the job_id


Now I searched for the Job_id in the System Maintenance sub plans.


I then used the delete statement above to remove the sub plan.  Then I was able to go back to the jobs and delete the job.

Thanks to Moshin’s DBA Blog for help on this.   Moshin’s DBA BLog

Using Contained Database to allow users to be copied with SQL Database

Since SQL Server 2012 there is a feature of SQL server that helps make copying databases easier.  Its called contained database.  This will allow a database to be copied along with the user logins.

Normally user login information is stored in the Master Database on SQL Server.  That is why when you copy a database to another server the logins are missing and have to be scripted separately.   With the Contained Database this is no longer necessary.

First the feature has to be enabled on the server

sp_configure 'contained database authentication', 1;

Next the individual database has to be set to support containment.

Alter DATABASE demodb

Now we can add a user to the database with the following;

USE demodb

CREATE USER TotallyContained



The TotallyContained user only exists in the demodb database.  If I were to copy the database to another server, the TotalContained user will show up with sames access rights simplifing the process of moving or copying the database.



How to Script All SQL Jobs IN SSMS

The fastest way to script all of the SQL jobs for SQL Server is to go to VIEW menu and select the Object Explorer Details.  Now navigate to SQL Server Agent in the Object Explorer and select Jobs.  In the Object Explorer Details window, select all of the jobs and right click.  Select Script Job as,  Create To, New Query Editor Window.  This will return a script that creates all of the jobs.j

Removing or Replacing Bad Dates in Microsoft SQL

Sometimes you receive data from other sources that are suppose to be dates but are not entered correctly.  The roman classic I was born in ’12/15/0010′, or the time traveler ’03/23/99999′.  Sometimes its just junk like ‘tbd’ etc.  This is where try_cast or try_convert is essential.

Try_Cast or Try_Convert has been available since SQL 2008.  These will prevent an error to stop your query.

select TRY_CAST('1/1/2016' as date)
select TRY_CONVERT(date,'1/1/2016')

These will both successfully return a date of 1/1/2016.  But if you try to process the following:

select try_Convert(date,'1/41/2016')

This will return a null instead of an error.  You can wrap the try with a coalesce and return a default value

select coalesce(try_Convert(date,'1/41/2016'),'1/1/1900')

This will return 1/1/1900 and your query completes.

Dynamically Disabling Steps in SSIS

Often I find myself wanting to choose dynamically between different steps in  a package or being able to turn off a step without having to actually modify the procedure.

This can be accomplished dynamically by disabling a step via a variable or parameter.  In this example I have a loop that processes a set of college clubs.  The query loads a list of clubs.  I would like to have a different process for The Physics and Astronomy club.  Maybe eventually each club will have a different process of its own.  One way to make this happen is by controlling what is enabled.  Below is the package.


The SQL Load List of Files loads a table of the clubs to a source object that is used in the for each loop.   The Name of the club is assigned to  a variable called ClubName.


I then modify the step called Physics and Astronomy.  Open the properties of the Physics and Astronomy step and add an expression for the Disable feature.DisablePackageExample3

I test to see if the ClubName is not equal to Physics and Astronomy Club. If it isn’t then Disable is set to true and this step will not run.  If the ClubName is set to Physics and Astronomy Club then the step will continue.   Since I only have one other step for all other clubs.  I set the Disable for this step when ClubName is equal to Physics and Astronomy Club.  DisablePackageExample4

Now as I loop through the clubs,  the Physics and Astronomy Club step will only run for that club and All Other Clubs step will run for everything else.

Convert a date column to YYYYMMDD format

The YYYYMMDD format is very useful in organizing data for filenames, sorting, etc.

Here is one way to generate that  format.

SELECT CONVERT(VARCHAR(10), getdate, 112)

This is the method that works well in sql.  If you want to do the equivalent in an expression in SSIS then you can use the Year,Month,Day functions.

Year(getdate()) *10000 + MONTH(getdate())*100+ DAY(getdate())

This will result in a integer value in the form of YYYYMMDD.   You can then convert to a varchar.