Power BI DIVIDE DAX Function

 In Power BI, when dividing 2 number it is advised to use the DIVIDE() DAX function instead of the divide operator (/).

Syntax: DIVIDE(<numerator>, <denominator> [,<alternateresult>])

Example: % of Total Sales Amount = DIVIDE([Sales Amount], [Total Sales Amount],0.0)

Source: https://learn.microsoft.com/en-us/dax/best-practices/dax-divide-function-operator

Power BI Row Level Security

Power BI has the capability to filter data by user.  One of the ways to do this is by using the email address of the user that is signed in to Power BI using the UserPrincipalName() DAX function.

This can be done using a SQL table, CSV file, SharePoint list, etc. to manage what users have access to.  In this example I'll use a SQL table with a foreign key constrain to my crm.Contacts table to manage which contacts a user has access to.

Step 1. Create a SQL table for Row Level Security.

Step 2. Add records to the pbi.RowLevelSecurity table.

INSERT INTO pbi.RowLevelSecurity(EmailAddress,ContactID) VALUES ('bespencer1@gmail.com',5);

Step 3: Add the pbi.RowLevelSecurity table to your Power BI dataset.  Do this in Power BI Desktop

let

    Source = Sql.Database(".", "MyDB"),

    pbi_RowLevelSecurity = Source{[Schema="pbi",Item="RowLevelSecurity"]}[Data]

in

    pbi_RowLevelSecurity

Step 4.  Create the relationship between pbi.RowLevelSecurity and the crm.Contacts table.  

PowerBI will automatically do this for you if you have a Foreign Key in SQL Server.

Set the relationship to "Both" cross filter direction and check the "Apply security filter in both directions".

Step 5.  Create a Power BI Role and add this table filter on pbi.RowLevelSecurity

[EmailAddress] = userprincipalname()

Step 6.  Test the Row Level Security confirmation using the "View as" option.  

Check the box beside the role you created in the previous step and check the box beside Other user. 

Enter the email address of the user you want to test and click OK.  Power BI is not applying the Row Level Security filter to your data.

Step 7.  Publish to Power BI Online.

Publish the Power BI report to Power BI Online using the "Publish" button in Power BI Desktop.

Step 8:  Grant users Viewer access to the Power BI report.

Open the Workspace the Power BI report was published to.  Click the Manage Access button and add users with "Viewer" access to the workspace.  You can also grant users Viewer access to the Power BI report only depending on your security needs.

Step 9:  Add members to the Row-Level Security role you created.

Open the Workspace the Power BI report was published to.  Click the "More Options" button next to the Dataset and select "Security".

On the Row-Level Security screen, select the Row Level Security role you created and "Add" users as members of that role.  

Please note that Row-Level Security only works for user with "Viewer" access.  Users with elevated access including Member and Admin will have access to all of the data included in the dataset.

 

Power BI Calculate DAX Function

In Power BI, I use CALCULATE in almost all measure.

Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

Here is an example of the CALCULATE function where is specify which Date relationship I want the calculation to use when determine the total number of unique Air Tickets, excluding Refunded and Voided tickets.

Air Tickets = CALCULATE(DISTINCTCOUNTNOBLANK('Air Ticket'[Ticket Number])

, USERELATIONSHIP('Calendar'[Date],'Air Ticket'[Issued Date])

, KEEPFILTERS('Air Ticket'[Ticket Status] <> "Refund")

, KEEPFILTERS('Air Ticket'[Ticket Status] <> "Voided")

)

Source: https://learn.microsoft.com/en-us/dax/calculate-function-dax

SQL Server List Partitioned Tables with Row Count and Page Size

SQL Server script to list database tables that are partitioned with the partitions schema, partition function, row count, page size, and partition range.

SQL Server Create Partitioned Table

 SQL Server script to create a partitioned table.

DIY Emergency Meals

DIY Emergency Meals

• Electrolyte Mix (sugar-free)
• Protein Bar or Granola Bar
• Trail Mix
• Single-serve Light Tuna
• Single-serve SPAM
• Instant Brown or White Rice
• Single-serve Peanut Butter
• Single-serve Instant Coffee
• Hard candy
• Salt and Pepper packets (pickup at fast-food restaurants)

Once you have collected your emergency meal items, place them in a quart size zip lock bag, date, and store in a cool dry place.

Get Home Bag

This is a bag you may want to keep in your car when commuting to work to give you the best chance of getting home in case of bad weather or other incident that may hinder your normal commute home. 

Always keep enough gas in your car to get home x 2.  Gas stations may not have power to run the gas pumps and you might have to take alternate routes or back track if roads are closed/blocked.

1. Map with optional routes to get home.   Look for alternate driving routes and hiking routes.
2. Car Emergency Kit
3. Extra money/cash.  If you need to get gas and credit card machines are down, cash will get you enough gas to get home. 
4. Water bottle with water filter.
5. Backpack with First Aid Kit, Hiking Essentials, and Emergency Meals.  
6. Hiking shoes.  These should already be broken in to prevent blisters.
7. Weather appropriate hiking clothing.   Layers are always best.
8. Self defense items.