Advertisements

How to count per-row occurrence of multiple words or phrases across multiple columns in Power BI

Sometimes you want to know how many times words and phrases appear across multiple columns. For example, if we want to know how many times “Cat” OR “Mouse” appears in each row, we would get:

Column 1 Column 2 Column 3 Word Count
Cat Yarn Mouse Cat Dog 3
Dog Cat Horse Hay Dog 1
Cat Cat Cat Mouse 4

Advertisements

What is the Related() function in Power BI?

RELATED() pulls values from different tables based on relationships established in the relationships tab. This is useful and makes it so we don’t necessarily need to append or merge tables to get the same result.

For example, we can create a new calculated column in our fact table Sales that pulls in related information from two other tables without needing to append or merge tables:

PBIDesktop_2018-09-25_10-32-08

How to add rows for dates between start and end dates in Power BI date range data

screenshot1

Screenshot from appsource.microsoft.com’s app detail page.

In Power BI there are several custom visuals available, such asĀ Elastacloud’s Calendar Visual, that show the density of events over time. However, if your data includes date ranges (start date with a different end date, such as task/project lists), visuals like these will only understand one of the two dates (whichever you’ve chosen for the data value) and none of the dates in between will be accounted for, making your data visualization incorrect.

You can, however, “expand” these date ranges or durations to create rows for all the dates including start, end and those in between. This way each date that’s part of the range is then graphed appropriately.

How to change Power BI report page size

Did you know you can adjust the page size of your reports in Power BI? Each tab/page of your report can be a unique size specified by you down to the pixel. This comes in handy for creating “widget-like” visuals for embedding or for creating reports for print and optimal display on various screen sizes.

Your options are:

  • 16:9 (default)
  • 4:3
  • Cortana
  • Letter
  • Custom (in pixels)

Power BI: Calculate next year’s amount in previous year’s row

next year

The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.

How to create a powerful date table or DateKey in Power BI

DateKeys are essential for relative time measures. In “manage relationships” you tie the ‘DateKey'[Date] to a date field in each of your data sources. Giles Walker shared an excellent solution for a robust DateKey that includes measurements and calculations you’re sure to find useful. Here’s that same solution I’ve modified and expanded to be as useful as possible.