After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
Columns vs Measures is a particularly misunderstood concept
in Power BI.
Often a user tries to create a Measure and realizes that the data item they want to use isn’t available and its hard to understand why. After a few times of this happening the user switches to using calculated measures all the time, losing the functionality of the measure.
The evaluation context is important to note for both of these column creation methods. This is the environment under which the formula is evaluated.
Calculated Columns
The evaluation context for the calculated column is
performed for each row
Example 1, I need to create an Order Total from Unit Price and Order Qty
This is correct for the Calculated column as the order total
is created for every row.
Misunderstanding the measure and trying to do the same thing as a measure causes issues
Note that Unit Price not in the list. It is not available to
the measure.
We will answer Why later
Calculated column data is stored in xVelocity in-memory
database. The calculation is made before the model is queried by the user. Calculated columns are good because there is
a smaller virtual memory requirement when the user is interacting with the
reports. However they take up more
storage in the database.
There are other advantages to the calculated column
Slicers
The cost bucket allows us to create a slicer in Power BI based on Low medium or high values. The DAX looks at each row and the new column is set before the model is queried.
Measures
Measures are used to calculated aggregates like Sum or
Average.
Measures are created at the time of the query so they are
not stored in the database.
Why could we not see Unit Price when we changed the 1st
Example to a measure? Because Unit Price is at row level. The measure needs to
be applied to aggregated values.
Example 2 – Get the order total of the low value items
First of all, notice the Sum around Order Total. The Sum
aggregates the order total and allows this item to be available to the DAX
query
Cost bucket is available because all Low cost buckets are
grouped first in order to find all the Low values.
Next Notice that the Sum of Low orders is not available in the data view pane because it doesn’t exist in the data in the way a calculated column does. We can see the Measure under fields to use within reports
Measures are evaluated in this specific way:
The evaluation context is examined. DAX is
selecting Low Priced items
The evaluation context is evaluated against the
table. Order Total is being filtered on Low Prices
The Aggregation is applied SUM
Results are returned
What to use when
Calculated columns can be used when each row needs
evaluating
And we have 3 toggles moving to 3 hidden visuals, with pie
as the starting point
And we have a grouping of Toggles on and Toggles off with
toggles hidden based on what bookmark we are on
But say we wanted to add another panel on the page with
three other visuals. First of all we
need to update the page and our Bookmarks and selections
We need to make space for the 2nd panel. Move the
grouped toggles, Text and make each visual smaller:
With Pie Bookmark selected, Move Toggle on group and Toggle
Off group. Move the toggle text
You don’t need to update the bookmark because we haven’t yet
amended anything in the selection
Click on Funnel . The toggles are already done so make the
funnel visual smaller
Do the same with Map
Now we are ready to make some changes to the selections.
Toggle On Rename to Panel 1 Toggles on. Toggle 2. Toggle off Rename to Panel 1
Toggles Off
Lets group the Visuals for Panel 1 also. With CTRL held down
select the 3 visuals. Then right click on the pie chart and select group. Rename
to panel 1 visuals
Open up this grouping and note that the correct visuals are
still hidden when you select each bookmark
Finally lets rename the bookmarks we have to include Panel 1
We are now ready to implement panel 2
Ive added 3 new visuals. Line chart, Tree Map and a card. We
will start with the line chart. Next Tree Map and Finally the Card
In Selection Order them and Renamed them Line Chart and Tree
Map. Next put them on top of one another.
I have then selected all three by holding down the ctrl key.
Clicked on the visual and chose Group, Ive renamed it Panel 2 visuals
Note if you move things in the selection pane with your groupings open, then seem to get added to the grouping above them. If you move objects, close the groupings first.
Next we want to add 3 more bookmarks. Call them Panel 2 Line
Chart, Panel 2 Tree Map, Panel 2 Card
Within Bookmarks … Line Chart should have data ticked, the
other 2 should have data unticked as per our previous bookmarks
Next we want the 6 toggles (3 On and 3 Off) Lets add the On
ones first. The same images from part 1 of this post will be used
Call the 3 On buttons, Line On Img, Tree Map On Img, Card On
Img
Again we can group then Panel 2 Toggles On
Next add the 3 Off buttons over the On buttons. At this point
its really good to get the images in exactly the same place and size as the on
buttons.
Call the 3 Off buttons, Line Off Img, Tree Map Off Img, Card
Off Img
Select, Group and call them Panel 2 Toggles Off.
Finally add your panel 2 text box
You now have everything you need to get started to show and hide bookmarks.
However, you only want to show and hide visuals for panel against the panel 1 bookmarks. You don’t want any changes you make in panel 2 to affect panel 1.
We now need to change every single bookmark from All Visuals
to Selected Visuals
Lets go through each bookmark and Ensure that the right information
is set
Click on Panel 1 Pie bookmark. The only Selection groups that matter are Panel 1 Groupings. Close all the Panel 2 groupings in the selection pane
Note that all the right visuals are hidden because we have
already sorted out this visual
Close all Panel 1 Groups
With CTRL held down select Panel 1 Groupings containing all the visuals. Then Select … against Panel 1 Pie Bookmark and Update Visuals. Because its now Against Selected Visuals only Panel 1 Groupings will be affected
Open up Panel 1 Panes and Move to Panel 1 Funnel
Note that you now have to recreate shown and hidden visuals
for Funnel. Again with The Panel 1 groups selected Update the Bookmark
(Selected Visuals Update)
Next Click on Panel 1 Map. Again, Note that you have to recreate
all the selections
And again with Panel 1 Groupings selected Click on Save
You can now click on each bookmark and note that panel 1
changes. Panel 1 is working.
Time to move to Panel 2. Close all Panel 1 groupings under
Selection and Open Panel 2
The First setting for the Line Chart. On Toggles hidden for Tree Map and Card. Off
Toggle Hidden for Line Chart. Tree map and Card visuals hidden.
With Panel 2 groups selected. Update Panel 2 Line Chart
Bookmark
Now Set up Panel 2 Tree Map Bookmark
And Finally Panel 2 Card Bookmark
Finally, We need to make sure that all our Buttons are active.
This is relevant for everything in Toggles Off panel.
We know that all our Panel 1 off buttons go to the right place
because we set this up in the last blog so lets do Panel 2 Toggles Off
We have the Line Chart Bookmark selected so for both Off
None hidden visuals, set the action to the appropriate Bookmark.
We need to move to another bookmark to do line because its
off. Move to Panel 2 Tree Map. From here, click on Line Chart Off Img and Set
the Action in Format to Panel 2 Line Chart.
Note. If you forgot to save at any time. Simply, Highlight the panels. Ensure the correct images are shown and hidden and then update. Always update with the Correct Groups selected
Before Publishing, lets move to the bookmarks we want as our
top bookmarks. Click on Panel 1 Pie Bookmark
Click on Panel 2 Line Chart
And Publish
Go to Power BI Service and check that its all working
Great, we now have two toggle panels. Both Work.
Just remember, Add eveything into groups
Panel 1 Off Images
Panel 1 On Images
Panel 2 Off Images
Panel 2 On Images
Panel 1 Visuals
Panel 2 Visuals
Ensure Selected Visuals is selected in Each Bookmark.
Ensure Actions are set on the correct visuals to move to the selected Bookmark
When ever you update, ensure the groupings are Selected.
I originally had one file to import into a SQL Database Survey.txt
The files are placed in Azure blob storage ready to be
imported
I then use Data Factory to import the file into the sink
(Azure SQL Database)
However, the data is actually in one worksheet a year. For
full logic I need to be able to add a worksheet to the blob storage to get it
imported and each worksheet will contain the year.
This means I need to change the Source and Pipeline in Data
Factory
First of all remove the file name from the file path. I used
1 file to set up the Schema. All files are the same so this should be OK.
Next I go to the Pipeline and set up the Wildcard in here Survey*.txt
When the Pipeline is run, it will take all worksheets against for example Survey
Debbies Microsoft Power BI, SQL Fabric and Azure Blog 