Power BI: DAX: Using Multi Select Slicer show different charts for each selected item.

1. AllSelection = IF(NOT(ISFILTERED(PLDeviceID[VideoSportClean])),"All",
CONCATENATEX(ALLSELECTED(PLDeviceID[VideoSportClean]),PLDeviceID[VideoSportClean],","))&","

2. AllSelectionCount = LEN([AllSelection])-LEN(SUBSTITUTE([AllSelection],",",""))

3. FirstMeasure = CALCULATE(SUM(PLDeviceID[LiveStreamStartsEvent19]),FILTER(PLDeviceID,PLDeviceID[LiveStreamStartsEvent19]>0),FILTER(ALLSELECTED(PLDeviceID),PLDeviceID[VideoSportClean] =[FirstSelection]))

4. FirstSelection = LEFT([AllSelection],SEARCH(",",[AllSelection])-1)

5. SecondMeasure = CALCULATE(SUM(PLDeviceID[LiveStreamStartsEvent19]),FILTER(PLDeviceID,PLDeviceID[LiveStreamStartsEvent19]>0),FILTER(ALLSELECTED(PLDeviceID),PLDeviceID[VideoSportClean] =[SecondSelection]))

6. SecondSelection = MID([AllSelection],SEARCH(",",[AllSelection])+1,SEARCH(",",[AllSelection],SEARCH(",",[AllSelection])))

Running Total 
Last Available Value

DAX query examples: DAX Running Total:

DAX Running Total:

git clone git@gist.github.com:92e8e3b7a1a212549af4.git gist-92e8e3b7

How to find if data has been loaded into database

Here is a simple way to find if the data has been loaded into database without checking every table one after another. This returns a single statement which has count of all the records in all the tables. You can also provide the table schema to narrow your query process.


DECLARE @sql VARCHAR(max), @tableSchema VARCHAR(5)
SET @sql = ''
SET @tableSchema = 'DW'
SELECT @sql = @sql + 'SELECT COUNT(*) as Count,'''+TABLE_NAME+''' AS table_Name FROM '+TABLE_SCHEMA+'.' +TABLE_NAME +' UNION '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @tableSchema
SET @sql = LEFT(@sql,LEN(@sql)-LEN('union'))
SET @sql = @sql+' order by Count'
EXEC (@sql)

DAX References

Read more about the types of DAX functions: 

• Date and Time Functions 
• Filter Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions
• Text Functions
• Time Intelligence Functions
•  Some Additional links

DAX Operator reference:

• Operator Reference
• Appendix DAX Reference

For more resource check:

• DAX Resource Center.
• How filters behave.

For those who want to relate DAX with MDX and SQL, kindly refer the following link:

• Converting MDX to DAX

Adventure Works database:
http://www.ssas-info.com/analysis-services-faq/29-mgmt/242-how-install-adventure-works-dw-database-analysis-services-2005-sample-database

Last Non Empty Value in DAX

While creating a Dashboard where the data comes from both OLTP(transaction data) and OLAP (analysis data), the common issue we face is OLAP chart shows "No Data Available" error while the OLTP chart gives the data. So we need to find a solution where we can find the last available value for the OLAP data, so that the dashboard becomes meaningful. This issue has been illustrated in the following example.
Scenario:
I have two tables-
1.FactAssetAllocation: Its a OLTP table. This table records all the asset allocation transaction details. The data is captured every day (or every month depending on your system).
2. FactCommitments. Its a OLAP table. This table records the commitment amount details for each of the client. The data is captured when ever there is a new commitment is done with the client.

Now i had to create a dashboard which has two charts:
1. Is an asset allocation bar-chart.
2. Is a table which show the commitment details for a given client.
The dashboard filter is Date and Client.

Problem:
When i select a Client (example Alan) and a Date (example 2011.11.09) You find there is Asset Allocation data for the given client and date but there is no commitment details.

This is because the Asset Allocation has data for Client - Alan, Date - 2011.01.09, but there is no data for Client -Alan on the same date. the commitment for Alan was done on 2011-04-12. So this data has to be shown in the commitment status chart.
So now we need to get Last Non Empty (Last Available) Commitment value for the given Asset_Allocation[Date_key].
If i had to write a SQL query it will be:

SELECT Commitment_Amount 
FROM DW.Fact_Commitment  
                      INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
WHERE Date_key in 
        (
          SELECT MAX(C.Date_Key)
          FROM DW.Fact_Commitment C 
                      INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
        )

If the same query needs to be written in DAX:

CALCULATE(
MAX(Commitments[Commitment])
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] =CALCULATE(
MAX(Commitments[Date_Key])
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] <= MAX(AUM[Date_Key])
)
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
)
)
)

The Blue section highlighted gives the Last Available Commitment Date for which we need to find the commitment value.
More Links: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/08/thinking-in-dax-counting-products-in-the-current-status-with-powerpivot.aspx

How to preserve the font style properties in SSMS

I had a requirement where i was asked to store the input data to be stored in the user font style format that we receive. we were asked to store data with font style like bold, italics, fonts with indicators like red or green, sentences with new line etc... to be stored as is. The challenge was t-sql always stores all the data in the standard format and there wasn't any way to store it with the styles. 

So one method which i have found is to store the data in the database with html tags: like '<b><font color="red">Worse</b></font>' as Column_A. 

You can store all the details from the client as html tags with in a table in the database for the later use. And when required we can pull the data through an external application.

For instance write the following query in your SSMS: 

SELECT ''<htmL><b><font color="green">Better</b></font></html>'' AS [Status]

Choose Result to File option (as shown in the screenshot)  in the tool pane.

Execute the query. 

A pop up window appears, specify the location where you want to store the file.

Store the result into a  .doc file.

The word "Better" is saved as bold green in color.

Note: If you store the file as .txt file, the result is saved as it is with the html tags. 

BI Semantic Tabular Model Reporting

SQL Server 2012 has provided customers with large-scale data warehousing and analytical solutions backed by IT management and insights. New and enhanced self-service capabilities allow users to explore and unlock new insights from information spread across structured and unstructured data sources, both on-premises and in the cloud. 

Why BI Semantic Model (BISM)

The BI Semantic Model is a new hybrid data model that offers the powerful analytical capabilities of multidimensional data models as well as the simplicity and familiarity of tabular (that is, relational) model. 

Why BISM Tabular Model

As part of the BI Semantic Model, SQL Server 2012 Analysis Services introduces a new tabular modeling capability that allows users to build a BI model using exactly the same processes that were introduced in PowerPivot for Excel. Users can quickly bring together data in tables and build relationships using the DAX query language to apply business logic.  


The following section explains simple steps involved in developing a BISM tabular model.

Steps to develop your first BISM Tabular model

The following are the steps involved to develop your first BISM Tabular Model.

1. Install an Analysis Services Instance Running in Tabular Mode: 

 Either you can add new features to the existing instance or create a new instance for Tabular Model.

a        a)    To add new instance kindly follow the steps:

     b)  Select the Server Mode: Tabular Mode and add yourself as the administrator.

     c)   Verify if the tabular model is installed by connecting to the analysis server instance name that you have just created.

2. Kick Start:

The following steps needs to be followed to create a new BISM project:

  a)  Creating a BISM project: Open BIDS(Business Intelligence Development Studio) and click on new project. There are three types of project template:

        i)  Analysis Service Tabular Project: Use this template to create an Analysis Service project with tabular models.

ii) Import from PowerPivot: Use this template to create a tabular project by extracting the metadata and data from an existing PowerPivot workbook.

         iii)  Import from Server (Tabular): Use this template to create a tabular project by extracting the metadata from an existing tabular AS server

For our development choose Analysis Service Tabular Project.

      b)  Importing data: Double click the model.bim. This will open a container to browse all the tables in the model. At present its empty as we have not imported any data from database. 
To import data select Model from Menu bar and click on Import from Data Source.

 
A Table import Wizard pops up with different data sources to which the model can be connected. 
  Select Microsoft SQL Server and click next. 
  Provide the required details to connect to the relational database. 
  Next, choose the dimension and fact tables and click finish. 
  Now you have successfully imported data into your model.

3. Creating Relationships: 

You can create relationships between tables by switching to the diagram view (You can switch from grid view to diagram view by selecting the icon at the bottom right corner). If the tables already have foreign key relationships established they will be retained, otherwise you can create relationships between the tables manually.

4.  Creating Measures: 

Measures are created in the grid view. Switch to grid view and select the fact table. Choose the column for which u want to create the measure and select Sum from the Toolbar. This will create a measure for Sum of fact value, here sum of SalesAmount in my case. Any measure can be created with any type of calculation.

5.  Make your Model User Friendly: 

Hide the columns which is not meaningful for the user.
For example: Surrogate keys, foreign_Keys, audit columns, etc. 


You can do this either by setting the hidden to true in column property window else right click the column and select "Hide from Client Tools"

Give user friendly names to the columns and tables.

6. Creating Hierarchy: 

Switch to diagram view. Select the columns that needs to be added to the hierarchy, right click and select Create Hierarchy if the hierarchy is been newly created else choose Add to Hierarchy and choose the hierarchy name.

7. Create Calculated Column and Measures:

We make use of DAX (Data Analysis Expression) to create calculated columns and measures. There are more than 30 new functions added like distinct, count, sum, rank, lookupValue etc. 
To create a calculated column in the grid view add another column with the calculation. Its similar to named calculation in OLAP database and evaluated at the row context.

For example: [User Full Name] := [User First Name] + " " + [User Last Name]

To create a calculated measure, add your calculation at the measure grid. The values are evaluated at the filter context. These are placed at the values area of Power Pivot tables.

For example: [Average Sale] = AVERAGEX ( Sales, Sales[Amount])

8. Changing the Source Query and Adding New Tables:

We can also have the flexibility to change the source query of each table. To attain this switch to Data View and select the table. In the property window select Source Data (...) and switch to query editor and write your query here.

You can also add related dimension and fact tables at the later point of time to the model by choosing either "import from Data Base" or choose "existing connection"  in the toolbar.

9.  Deploying Model:

Ensure the deployment options are set correctly. To check right click the solution and select properties. Edit the deployment options and click ok. Now your model ready and you can deploy by choosing Deploy in the solution right click options.

10. Reporting:

You can also analyse the data in excel by selecting Excel symbol (Analyse in Excel) in the Toolbar. A pivot table is created by connecting to the semantic model just created. You can create excel dashboard reports, perform analysis of data easily.

Conclusion

The user experience of creating tabular data models in PowerPivot is similar to the user experience of creating an SSAS tabular project in SQL Server Data Tools. BISM Tabular models are similar to Excel and include easy-to-use formulas which make them useful for creating solutions with high agility and time to availability. For more informantion and additional resources follow the link below.

Reference Sites 

• www.kohera.be
• http://www.powerpivot-info.com/
• Power Pivot Overview 
• Denali Breakthrough Insight
• BISM model

Finding a particular string in the database

Sometimes, you may need to find a value in the string columns (char, varchar, etc) in all

the tables in a database. This can be for many reasons for instance, say i need to change that value to something else or may it be to just check the existence of the string in the database. Most of the time we end up doing is a manual search by querying each table, the lay man search!

Here is a simple query which does it for you:

DECLARE @sql VARCHAR(MAX),@search VARCHAR(100),@tableSchema VARCHAR(100)

SET @sql=''

SET @search='Alpha' -- 'your search string'
SET @tableSchema='DW'

SELECT

@sql=@sql+

'SELECT DISTINCT ''['+c.table_name+']'' AS TABLE_NAME,'''

+c.column_name+''' AS COLUMN_NAME,['+c.column_name+'] AS COLUMN_VALUE FROM ['+c.TABLE_SCHEMA+'].['+

+c.table_name+'] WHERE ['+c.column_name+'] LIKE N''%'+@search+'%'' UNION ALL '

FROM

INFORMATION_SCHEMA.COLUMNS AS C INNER JOIN INFORMATION_SCHEMA.TABLES AS T ON C.TABLE_NAME=T.TABLE_NAME

WHERE

T.TABLE_TYPE='BASE TABLE' AND DATA_TYPE LIKE '%CHAR%' AND T.TABLE_SCHEMA = @tableSchema

SET @sql=left(@sql,len(@sql)-10)

EXEC(@SQL)



//after where column name: '] = N'''+@search+''' UNION ALL '