Arun Gupta

Wednesday 6 June 2018

Unlabelled Power BI Means (Any data, Anywhere, Any time)

Power BI Means (Any data, Anywhere, Any time)

by on

                 Any data, Anywhere, Any time



Why Power BI:-
  • Machine learning
  • Mobility
  • Advanced analytics
  • Cortana Integration
  • Lower upfront costs
  • APIs for integration
  • Customization
  • User- friendly
What is Power BI:-
A business intelligence cloud service by Microsoft.
It provide both technical and non -technical users with tool for 
  • Aggregating
  • Analyzing
  • Visualizing
  • Sharing data
  • Developers 
  • Subject matter experts
  •  IT Professionals
  •  Business Analysts
  • Power Query:- Data mash up and transformation tool. 
  • Power Pivot:-  In-memory tabular data modelling tool.
  • Power View:-  Data visualization tool.
  • Power Map:-  3D Geo-spatial data visualization tool.
  • Power BI Service:- It is a cloud-based business analytics service that enables anyone to visualize and analyze data with greater speed, efficiency, and understanding
  • Power  BI Q&A:- Natural language question and answering engine.
  • Data Management gateway:- The Data Management Gateway is needed to connect your on premise servers with Power BI in the cloud.  If you want to refresh your data  in the cloud with data that is on premise, you will need to have the Data Management Gateway configured and available for your tenant.
  • Data catalog:-  Data catalog solutions foster search and efficient reuse of existing data in popular business intelligence, self-service data preparation and data discovery tools
  • Visualization
  • Datasets
  • Reports
  • Dashboards
  • Tiles









Different types of filter in Power  BI:-
  • Page level  filter :- applies to all the visuals on the report page
  • Visual level filter:- applies to a single visual on a report page
  • Drill through filter:- applies to a single entity in a report
  • Report  level filter:- applies to all pages in the report

 lets you do basic calculation and data analysis on data power pivot model function language used to compute calculate column  and calculated.

 For example:-Total amount=sum(product(amount))

 Most common DAX Functions:-

 Aggregate functions:-
  • MIN
  • MAX
  • Average
  • SUM
  • SUMX

 Counting functions:-
  • DISTINCTCOUNT
  • COUNT
  • COUNTA
  • COUNTROWS
  • COUNTBLANK

 Logical functions :-
  • AND
  • OR
  • NOT
  • IF
  • IFERROR
  • SWITCH

 TEXT Functions:-
  • REPLACE
  • SEARCH
  • UPPER
  • FIXED
  • CONCATENATE

 INFORMATION Functions:-
  • ISBLANK
  • ISNUMBER
  • ISTEXT
  • ISNONTEXT
  • ISERROR
  • ISFILTERED 
  • ISCROSS FILTERED
DATE Functions:-
  • DATE
  • HOUR
  • WEEKDAY
  • NOW
  • EOMONTH
  • DATE DIFF
Other Functions:-
  • VALUES
  • ALL
  • FILTER
  • CALCULATE
  • UNION
  • INTERSECT
  • EXCEPT
  • NATURAL INNER JOIN
  • NATURAL ALL LEFT
  • VAR(Variable)
  • GEOMEAN
  • MEDIAN




 SQL to DAX String Comparison:-


 Syntax in SQL Functions
Syntax in (Tabular and Power BI ) DAX functions
Name LIKE 'pyarungupta'
Table[Name] = "pyarungupta"
Name LIKE 'pyarungupta %'
LEFT( Table[Name], 11 ) = "pyarungupta"
Name LIKE '%pyarungupta'
RIGHT( Table[Name], 11 ) = "pyarungupta"
Name LIKE '%pyarungupta%'
SEARCH( "pyarungupta", Table[Name], 1, 0 ) > 0
Name LIKE 'pyarungupta %Technology'
LEFT( Table[Name], 11 ) = " pyarungupta "
&& RIGHT( Table[Name], 11 ) = " Technology "
Name LIKE ' pyarungupta % Technology %'
LEFT( Table[Name], 11 ) = "pyarungupta "
&& SEARCH( " Technology ", Table[Name], 1, 0 ) > 0
Name LIKE '% pyarungupta % Technology %'
SEARCH("pyarungupta* Technology ", Table[Name], 1, 0 )>0





SQL to DAX Filtering data:-
Syntax in SQL where condition
Syntax in (Tabular and Power BI ) DAX filter
SELECT *
FROM DimProduct
WHERE Color = 'Red'
EVALUATE
FILTER ( Product, Product[Color] = "Red" )

Or
EVALUATE
CALCULATETABLE ( Product, Product[Color] = "Red" )

                 
SELECT *
FROM DimProduct
WHERE Color = 'Red' AND ListPrice > 1000
EVALUATE
FILTER (
    Product,
    AND ( Product[Color] = "Red", Product[ListPrice] > 1000 )
)
Or

EVALUATE
CALCULATETABLE (
    Product,
    Product[Color] = "Red",
    Product[List Price] > 1000
)
or

EVALUATE
CALCULATETABLE (
    Product,
    FILTER (
        ALL ( Product[Color] ),
        Product[Color] = "Red"
    ),
    FILTER (
        ALL ( Product[List Price] ),
        Product[List Price] > 1000
    )
)
SELECT *
FROM DimProduct
WHERE Color = 'Red' OR Weight > 100
EVALUATE
FILTER (
    Product,
    OR ( Product[Color] = "Red", Product[Weight] > 1000 )
)
OR

EVALUATE
CALCULATETABLE (
    Product,
    FILTER (
        Product,
        OR ( Product[Color] = "Red", Product[Weight] > 1000 )
    )
)
SELECT *
FROM DimProduct p
WHERE
    Color = 'Red'
    AND ( SELECT SUM([SalesAmount])
          FROM [FactInternetSales] s
          INNER JOIN DimDate d
            ON s.OrderDateKey = d.DateKey
          WHERE s.ProductKey = p.ProductKey
            AND d.CalendarYear = 2006
        ) > 100000
    AND ( SELECT AVG([SalesAmount])
          FROM [FactInternetSales] s
          INNER JOIN DimDate d
            ON s.OrderDateKey = d.DateKey
          WHERE s.ProductKey = p.ProductKey
            AND d.CalendarYear = 2006
        ) > 3000
EVALUATE
FILTER (
    FILTER ( Product, Product[Color] = "Red" ),
    AND (
        CALCULATE (
            SUM ( 'Internet Sales'[Sales Amount] ),
            'Date'[Calendar Year] = 2006
        ) > 100000,
        CALCULATE (
            AVERAGE ( 'Internet Sales'[Sales Amount] ),
            'Date'[Calendar Year] = 2006
        ) > 3000
    )
)
OR
EVALUATE
CALCULATETABLE (
    FILTER (
        Product,
        AND (
            CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) ) > 100000,
            CALCULATE ( AVERAGE ( 'Internet Sales'[Sales Amount] ) ) > 3000
        )
    ),
    Product[Color] = "Red",
    'Date'[Calendar Year] = 2006
)
SQL to DAX Join Tables:-
  1. INNER JOIN
  2. LEFT JOIN
  3. OUTER JOIN
  4. CROSS JOIN
Syntax in SQL Join Tables
Syntax in (Tabular and Power BI )
SELECT
    d.Year, p.Color, SUM ( s.Quantity ) AS [Total Quantity]
FROM
    Sales s
    LEFT JOIN Date d ON d.DateKey = s.DateKey
    LEFT JOIN Product p ON p.ProductKey = s.ProductKey
GROUP BY
    d.Year, p.Color
EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Date'[Year],
        Product[Color]
    ),
    "Total Quantity", CALCULATE ( SUM ( Sales[Quantity] ) )
)
SELECT
    s.*, d.Year, p.Color
FROM
    Sales s
    LEFT JOIN Date d ON d.DateKey = s.DateKey
    LEFT JOIN Product p ON p.ProductKey = s.ProductKey
EVALUATE
ADDCOLUMNS (
    Sales,
    "Year", RELATED ( 'Date'[Year] ),
    "Color", RELATED ( Product[Color] )
)
SELECT *
FROM a
LEFT OUTER JOIN b
    ON a.key = b.key

SELECT *
FROM a
INNER JOIN b
    ON a.key = b.key
EVALUATE
NATURALINNERJOIN ( Sales, Product )
OR
EVALUATE
NATURALLEFTOUTERJOIN ( Product, Sales )
SELECT *
FROM a
CROSS JOIN b
EVALUATE
CROSSJOIN ( a, b )
SELECT *
FROM a
INNER JOIN b ON a.key = b.key
EVALUATE
FILTER (
    CROSSJOIN ( a, b ),
    a[key] = b[key]
)
SELECT
    s.*, d.Year, p.Color
FROM
    Sales s
    LEFT JOIN Date d ON d.DateKey = s.DateKey
    LEFT JOIN Product p ON p.ProductKey = s.ProductKey
EVALUATE
ADDCOLUMNS (
    Sales,
    "Year", LOOKUPVALUE (
        'Date'[Year],
        'Date'[DateKey], Sales[DateKey]
    ),
    "Color", LOOKUPVALUE (
        Product[Color],
        Product[ProductKey], Sales[ProductKey]
    )
)
SELECT
    d.Year, p.Color, SUM ( s.Quantity ) AS [Total Quantity]
FROM
    Sales s
    LEFT JOIN Date d ON d.DateKey = s.DateKey
    LEFT JOIN Product p ON p.ProductKey = s.ProductKey
GROUP BY
    d.Year, p.Color
ORDER BY
    d.Year, p.Color
EVALUATE
SUMMARIZE (
    ADDCOLUMNS (
        Sales,
        "Sales[Year]", LOOKUPVALUE (
            'Date'[Year],
            'Date'[DateKey], Sales[DateKey]
        ),
        "Sales[Color]", LOOKUPVALUE (
            Product[Color],
            Product[ProductKey], Sales[ProductKey]
        )
    ),
    Sales[Year],
    Sales[Color],
    "Total Quantity", CALCULATE ( SUM ( Sales[Quantity] ) )
)
ORDER BY Sales[Year], Sales[Color]
SQL to DAX Group data:-
SELECT
    OrderDate,
    SUM(SalesAmount) AS Sales
FROM  
    FactInternetSales
GROUP BY
    OrderDate
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Internet Sales'[Order Date],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] )
)
OR
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Internet Sales'[Order Date] ),
    "Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)
SELECT
    d.CalendarYear,
    SUM(s.SalesAmount) AS Sales
FROM
    FactInternetSales s
LEFT JOIN DimDate d
    ON s.OrderDateKey = d.DateKey
GROUP BY
    d.CalendarYear
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] )
)
ORDER BY 'Date'[Calendar Year]
OR

EVALUATE
ADDCOLUMNS (
    VALUES ( 'Date'[Calendar Year] ),
    "Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)
ORDER BY 'Date'[Calendar Year]
SELECT
    d.CalendarYear,
    SUM(s.SalesAmount) AS Sales
FROM
    DimDate d
LEFT JOIN FactInternetSales s
    ON d.DateKey = s.OrderDateKey
GROUP BY
    d.CalendarYear
SELECT
    d.CalendarYear,
    SUM(s.SalesAmount) AS Sales
FROM
    DimDate d
LEFT JOIN FactInternetSales s
    ON d.DateKey = s.OrderDateKey
GROUP BY
    d.CalendarYear
HAVING
    SUM(s.SalesAmount) > 8000000
ORDER BY
    d.CalendarYear
SELECT
    CalendarYear,
    Sales
FROM
    ( SELECT
        d.CalendarYear,
        SUM(s.SalesAmount) AS Sales
      FROM
        DimDate d
      LEFT JOIN FactInternetSales s
        ON d.DateKey = s.OrderDateKey
      GROUP BY
        d.CalendarYear
    ) YearlySales
WHERE
    Sales > 8000000
ORDER BY
    CalendarYear
EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Calendar Year] ),
        "Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
    ),
    [Sales] > 8000000
)
ORDER BY 'Date'[Calendar Year]
OR
EVALUATE
FILTER (
    SUMMARIZE (
        'Internet Sales',
        'Date'[Calendar Year],
        "Sales", SUM ( 'Internet Sales'[Sales Amount] )
    ),
    [Sales] > 8000000
)
ORDER BY 'Date'[Calendar Year]
SQL to DAX IN and Exists:-
Syntax in SQL IN and Exists
Syntax in (Tabular and Power BI )
SELECT DISTINCT EnglishCountryRegionName
FROM DimGeography
WHERE CountryRegionCode IN ('US', 'CA', 'AU' )
EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    OR (
        OR (
            Geography[Country Region Code] = "US",
            Geography[Country Region Code] = "CA"
        ),
        Geography[Country Region Code] = "AU"
    )
)
OR
EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    Geography[Country Region Code] = "US"
    || Geography[Country Region Code] = "CA"
    || Geography[Country Region Code] = "AU"
)
OR

EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    FILTER (
        ALL ( Geography[Country Region Code] ),
        CONTAINS (
            VALUES ( Selection[Country Region Code] ),    
            Selection[Country Region Code],
            Geography[Country Region Code]
        )
    )
)
SELECT DISTINCT
        ModelName
FROM    DimProduct p
WHERE   EXISTS ( SELECT NULL
                 FROM   FactInternetSales s
                 WHERE  s.ProductKey = p.ProductKey )
ORDER BY ModelName
EVALUATE
FILTER (
    VALUES ( Product[Model Name] ),
    CALCULATE ( COUNTROWS ( 'Internet Sales' ) ) > 0
)
ORDER BY Product[Model Name]
OR
EVALUATE
FILTER (
    VALUES ( Product[Model Name] ),
    NOT ISEMPTY ( CALCULATETABLE ( 'Internet Sales' ) )
)
ORDER BY Product[Model Name]
OR
EVALUATE
FILTER (
    VALUES ( Product[Model Name] ),
    NOT ISEMPTY ( RELATEDTABLE ( 'Internet Sales' ) )
)
ORDER BY Product[Model Name]
OR
EVALUATE
FILTER (
    VALUES ( Product[Model Name] ),
    CONTAINS (
        RELATEDTABLE ( 'Internet Sales' ),
        Product[Model Name],
        Product[Model Name]
    )
)
ORDER BY Product[Model Name]
SQL to DAX Projection:-
Syntax in SQL
Syntax in (Tabular and Power BI )
SELECT *
FROM Product
EVALUATE Product
SELECT [Product Id], [Product Name], [List Price]
FROM Product
EVALUATE
ADDCOLUMNS(
    DISTINCT( Product[Product Id] ),
    "Product Name", CALCULATE( VALUES( Product[Product Name] ) ),
    "List Price", CALCULATE( VALUES( Product[List Price] ) )
)
OR

EVALUATE
SUMMARIZE(
    Product,
    Product[Product Id],
    Product[Product Name],
    Product[List Price]
)
SELECT DISTINCT [Product Id], [Product Name], [List Price]
FROM Product
SUMMARIZE is equivalent to a SELECT DISTINCT statement
More details go through:-https://www.sqlbi.com/


Game in Power BI:-


Play Tic Tac in  Power BI




Play Sudoku in Power Bi





By at

8 comments:

Subscribe to: Post Comments (Atom)