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.

top5

The filter is a shown below.

filterrankx

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)

filterwithorgx

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

Top5withOrgX

 

 

Advertisements

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

search

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.

1-30-2017-8-43-58-am

The script will contain the job_id

1-30-2017-8-47-01-am

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

1-30-2017-8-51-56-am

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;
RECONFIGURE;

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

Alter DATABASE demodb
Set CONTAINMENT = PARTIAL;

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

USE demodb

CREATE USER TotallyContained

WITH PASSWORD = 'A)$*LaJSFS';

 

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