Power BI February 2022 Updates Dynamic M Query Parameters

Now supports SQL Server and more data sources

But what are Dynamic M Query Parameters and what does this mean?

It feels like they have been upgraded to use with direct query data sources so you can restrict the amount of data being asked for at the lowest level.

Lets have a look at a simple example using Taxi data from a Microsoft learning path.

First of all you need to open Power BI – Options and Settings – Options

Its in Preview so make sure that is ticked before continuing

Get Data Azure SQL Database (The guidance mentions SQL Server but it seems that both can be used for this test)

Load

Then go to Transform data.

Right Click on trip fares to get to advanced editor

let    
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db"),    dbo_TripFares = Source{[Schema="dbo",Item="TripFares"]}[Data]
in   
 dbo_TripFares

Its currently bound to a table but we need to bind it to a query for this process.

Click the cog against source.

Go into Advanced Options and add the SQL Statement

SELECT * FROM dbo.TripFares

And then go back and look at advanced editor

let   
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db", [Query="SELECT * FROM dbo.TripFares"]),    
dbo_TripFares = Source{[Schema="dbo",Item="TripFares"]}[Data]
in    
dbo_TripFares

So now, its nearly bound to a query but you will note that it looks like the table is erroring.

You can go back to Advanced Editor and change to

let
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db", 
[Query="SELECT * FROM dbo.TripFares"])in   
 Source

 We only need the query and not dbo_TripFares

Now we can add the  Dynamic M Query parameters. I will go for an easy one first as a demo.

And then I change the advanced code again

let 
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db", [Query="SELECT * FROM dbo.TripFares Where payment_type = '" & paramPaymentType & "'"])
in    
#"Source"

Note the new WHERE Clause that concatenates the value in our parameter

It will read in SQL SELECT * FROM dbo.TripFares Where payment_type = ‘CRD’

When it runs the first time you are asked to approve and you can actually see the SQL its going to use which is good. (Note I had to change to CSH to get the message up but I am running with CRD)

When it comes through its restricting to the selected payment type

We are going to change the code again

let    
filterQueryPaymentType = "SELECT * FROM dbo.TripFares Where payment_type = '" & paramPaymentType & "'",    
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db", 
[Query=filterQueryPaymentType])
in  
#"Source"

This sets the SQL command first and we pass the Filter query into the data source

Now we know that the query works. Lets use it in Power BI Reporting and Assign to table.

This will need a lookup table of all the payment types to work

I am going to simply create the reference table in M

let
Source = Sql.Database("dataengineering-sqlserv.database.windows.net", "taxi-data-db", 
[Query="SELECT DISTINCT payment_Type FROM dbo.TripFares"])
in    #"Source"

Close and Apply

Now bind the table to the parameter in Modelling tab

Click on Payment_Type column. Advanced. Bind to parameter

Click Continue

A multi select is not going to be used for this demo

I have added a quick table. the metrics have come through as strings and there will be lots of things you need to test in direct query mode but I will ignore for the time being.

I dragged in Payment type from the Payment Type lookup into a slicer.

Click the slicer and see your data change. every time you click the slicer a direct query will happen but only for the payment type selected, hopefully making things much quicker.

And there you go. You have set up a restricted direct query. This will help with any direct query reports you need to create based on real time data.

You are still hitting the SQL DB though a lot so this would need thinking out.

And remember, Direct query doesnt give you the full Power BI reporting suite so your reports may be more basic. And usually I like to work with Star schemas but here we have the extra complexity of lookup tables to work with the parameters.

I will be looking at a date time example soon hopefully. This is clearly an extremely important piece of the Direct query real time puzzle.

Power BI Admin APIs to return a list of email subscriptions

Get Dashboard Subscriptions brings back a list of everyone who has subscribed to a dashboard

What is a Power BI Subscription?

Subscriptions are a great way to assign yourself and other users to get emails regarding report content.

There are certain governance rules we follow.

  1. Report viewers views content via an app. We don’t want report viewers coming into the App workspace. we want them to see carefully selected and brought together content.
  2. If we use Subscriptions we want to push though a really nice screen shot of a report that changes and gets the users wanting to come and see more content within that app. therefore we always have a report or dashboard with visuals that don’t need scroll bars to engage the viewer so they want to see more.
  3. because of this, we want to be able to subscribe people to App content

Go to an App. Note you can add your Subscription here which is a link to the dashboard

for this example, the App dashboard is subscribed to

then we go to try it out from the Microsoft API Page

Dashboard Subscriptions

https://docs.microsoft.com/en-gb/rest/api/power-bi/admin/dashboards-get-dashboard-subscriptions-as-admin

and try the API

Add the Dashboard ID to parameters

But this is where logic is not quite working (the hope is that this will resolve fairly quickly). The above API doesn’t give you information back if you subscribe via the app. Only when you subscribe to the actual Dashboard in the workspace.

We want all our report viewers accessing the pre built app so this is where the information is most required.

When the user is added to a dashboard subscription in the workspace. The API is tested again.

What this can show us is anyone in the workspaces that has subscribed to the actual dashboard.  We want all viewers with App access.

Get report Subscriptions as Admin

https://docs.microsoft.com/en-gb/rest/api/power-bi/admin/reports-get-report-subscriptions-as-admin

This is the same as above but with reports

Get user Subscriptions as Admin

https://docs.microsoft.com/en-gb/rest/api/power-bi/admin/users-get-user-subscriptions-as-admin

I get my user ID from Azure Active Directory

And see what I’m subscribed too but again, only workspace content

Logically, I feel like our viewers should be subscribing through the apps

this is really good stuff but I feel like they need to resolve the issue with Apps. Apps are the go to areas for users to view content so this is where we want people to subscribe too.

If you look at the information coming back. Here we can see the artifact type is report but there is no where that mentions if the report is in an App or in the workspace and I feel like this is actually important information. I only know because I have tested against both the App and the workspace.

If this could be resolved these APIs would be really useful to help us understand the subscription uptake.

Power BI Sparklines

Sparklines are small charts you can add to a table or Matrix and are new for the start of 2022.

Above we have a Matrix showing Products by country and I would like to also see this measure on a timeline.

Select the metric Value : Add a Sparkline Button

Here we choose the measure Total Sales which has already been summarised. The Date will be used as the X Axis so we can see the trend across time.

Rename to Sparkline if you want (double click the value to highlight as editable) and now you can see the trends

And you can also format the spark line (Right at the bottom of formatting)

So Sparklines are great for adding you your tabular information, I think the line charts are pretty good.

Current restrictions

Only supports 5 per visual so if you have more categories that wont work.

Apparently Date hierarchies don’t work at the moment but they will in the future

It would be good to assign conditional formatting to the colours. So going up could be green. Staying around the same amber and going down red.

they really do bring tables to life though and are are really great addition to power BI

Power BI DAX – Create a percentage across days of week (ALLEXCEPT. ALL. DIVIDE)

We just looked at the following request

We have sold specific quantities of products. Can we look at a week for a year in a slicer and see the percentage of items sold for each day. Which days do we sell the most products overall?

We have the following model (Just the parts that will be required for this exercise

And thanks to the Power BI Forums for giving me some help on figuring this out.

What we want to end up with is:

  • A year Slicer
  • Product category on rows of a matrix
  • The Day Name from Date on the Columns of the Matrix
  • And % Quantity for that day based on all the days of the week as the value

Power Query Editor

At first I believed that I needed to add some kind of Ranking order for the DAX to use and as this will be a column its better to do in Power Query Editor (Or in the Sort DB)

To speed things up I created a Year Week Column in SQL DB consisting of the following examples

  • 202101
  • 202102
  • 202135

So the weeks 1 to 9 was padded out with a zero. I then formatted this to a number field and called it Year Week. Every seven rows within that week in the date table will have the same Year Week Number Applied

Measure

I can now create the measure. Lets have a look at it in a bit more detail

Order Quantity by Week % = Var varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), FILTER(ALL(DimDate), DimDate[Year Week]))
RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek)

And this measure is then set as a percentage.

First of all we create a variable. It gets the Sum of Order Quantity and it filters by using an ALL on the column Year Week we just created in the Date Table

“ALL Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied (On Year Week).”

And we return the sum of order Quantity (Which in the context is just for, for example Friday and Accessories by the Sum of Order Quantity

This appears to work

We have Implicitly filtered by Product Category and Day Name of Week.

the question here is, did we even need to set ALL on Year Week column in date. Could we have just said use the entire date

Order Quantity by Week % = Var 
varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), ALL(DimDate)) 

RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek)

This is working in the same way and makes sense to me. We are using all the dates in the date table. And removing the Filter will create a faster DAX query.

Its looking at the entire data set and we can see that everything totals to 100%. So for us, taking into account all years Thursdays look like a good day especially for accessories.

However we don’t want to analyse the full data set and when we add in a year slicer the logic fails

As you can see, the story is still true but its telling the wrong story. Now for Accessories we have 31% of sales happening in 2021, attributing to smaller percentages across the days.

So we want to change the DAX to accept the Year Slicer

Order Quantity by Week % = 

Var VarALLEXCEPT = CALCULATE(SUM(FactResellerSales[Order Quantity]), ALLEXCEPT(DimDate,DimDate[CalendarYear]))

RETURN 
DIVIDE(SUM(FactResellerSales[Order Quantity]), VarALLEXCEPT)

And this appears to now work just taking the year 2012 into consideration because we are using ALLEXCEPT

Thursday is definitely a good day.

“ELLEXCEPT Removes all context filters in the table except filters that have been applied to the specified columns.”

So we create a variable to get the Sum or Order Quantity with ALLEXCEPT our Date Slicer which in this case is the calendar year slicer

So we are using 2012 from the date Dimension in the slicer. And Bikes from Product and Thursday as the Week Name from date in the Matrix

We Divide the Sum of Quantity which has all the filters applied. Bikes. Thursday. 2021

By the Sum Of Quantity with all, Except we do apply the Calendar Year.

  • DIVIDE(50/100)
  • 50 is Bikes on a Thursday in 2012
  • 100 is Bikes in 2012

DAX is something I do struggle with a little. You think you understand it. Turn your head from the screen and its gone again.

But hopefully this goes a little way to understanding how this DAX in this specific context has been approached

Power BI AI Visual Smart Narratives

Power BI has given us the following AI visuals:

  • Key influencers
  • Q&A Visualisation
  • Decomposition Tree

And now we also have smart narratives to work with. Many companies want to create report that includes text in regards to the report they are looking at to help the user understand the key takeaways. This usually involves someone manually adding in text to the reports.

Not great for interactive reports that update on a regular basis. this is where smart narratives come in. Smart narratives create the text in an interactive way for you.

If its good, it should eliminate the need to add in information manually for your users, which is a huge win.

Lets have a look at it in action on the Adventureworks Report

At the most basic level you can simply drag smart narratives into your report

Smart narratives describes your data to you. This is all really good information. what happens when I select a data point on the report like select 2011 in Sales amount and order quantity

The narrative updates to tell you about just 2011 so our smart narrative is even better, It updates with every click the user makes.

For my Top 10 Products table, I want a smart narrative that just gives me information on this visual and not every visual on the page so I right click on the visual and choose Summarize

And again, we are getting good information for that specific visual. You need to think whether the visual can tell this story on its own or if narrative would help. I tend to see smart narratives as something that sits on a page to tell a story across all the visuals.

Changing the Summary

This is great but there will be times when you do want specific information in your smart narrative. I am going to remove the smart narrative for the visual, and replace with one for the entire products report page.

Click on the smart narrative and then +Value to add a value

I want it to tell me how many items have been sold of top value products. the red line tells me that it doesn’t know what sold means and we need to define this item .

Click on Sold and then Define Sold

Here I go to Field Synonyms in the Q&A Setup to define a new synonym for order quantity because order quantity can be expressed as quantity sold. I have added quantity sold as this is what has been searched for.

Back in Teach Q&A I ask the question again

And this time it understands what I mean. I submit this to train the Q&A. and this time we find this specific query because its in Q&A. I can now format the question to get an answer for the smart narrative

After saving, the number simply appears in the smart narrative. I deleted it and moved the cursor to the beginning on the smart narrative. then I wrote.

The quantity sold of top value products is:

And then clicked review, found the item and + to insert item to place it exactly where I want it

from this I can see that I can use the Q&A tool to format questions that I can then add into the smart narrative, adding synonyms where necessary to create even more easy to understand questions.

Smart narratives and Q&A work perfectly together.

Power BI October 21 Updates GetUserArtifactAccessAsAdmin API through to PowerShell

There have been some new API updates (September October Power BI 2021) and I thought I would see what they were all about and how you can use them starting with the API mentioned in the October updates.

I haven’t used these APIs before and I thought it would be interesting to see how the documentation holds up.

So the first start at https://powerbi.microsoft.com/en-us/blog/power-bi-october-2021-feature-summary/#post-17563-_Toc84518701

And this links you you to https://docs.microsoft.com/en-gb/rest/api/power-bi/admin/users-get-user-artifact-access-as-admin

So this API returns all the artifacts for a given user which I imagine would be really useful when someone leaves the company and you need to assess the objects that they may own.

To use the API you need to be Power BI admin or Global Admin. I have the Power BI admin so I am good to go.

Delegated permissions are supported. I imagine this needs a lot more investigation as to why and how you would set this up.

There are some limitations, For example you can only make 200 requests per hour. You can click on Try it to try out the API from this URL by logging into your account

But this API’s need you to get the userGraphId. my first question is, What is a userGraphID and how can I get it?

It is mentioned in the documentation. ‘The graph ID of user’

And to be honest that is unhelpful. I have no idea how to get this information to run the API. Its just assumed. I have also asked on the forums and no one else seems to know either.

So to just get an idea of how you would use these APIs, I’m working with one that doesn’t need the userGraphIDhttps://docs.microsoft.com/en-us/rest/api/power-bi/admin/groups-get-groups-as-admin#code-try-0

This will get me out all the objects in power BI and for the time being I just want the top 10.

I added the $expand name and value by creating the name and value and hitting the +

Then hit Run to make sure it works

So where do we use it? Open up Windows Powershell

We can run the commands via Windows Powershell. https://docs.microsoft.com/en-us/powershell/scripting/overview?view=powershell-7.1 ISE (Integration Scripting Environment)

powershell_ise.exe

And type in

 login-PowerBI

And now you can create your code

I simply added the URL from the Try it above (Without the Authorisation Line)

Value[0] Simply means give me the first element inside this command and run.

I can go even further an parameterise the URL by doing the following

Param(
[string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=10&%24expand=users%2Creports%2Cdashboards%2Cdatasets'
)
login-PowerBI
$result = Invoke-PowerBIRestMethod -Url $url -Method Get 
$workspaceContents = $result | convertFrom-Json
$firstworkspace = $workspacecontents.value[1]
$firstworkspace

This is great

  • But what if I want to automate this and, for example create a json file from it once a month?
  • And what if I don’t want to have to run it as the Power BI Admin?

Back to the original question regarding userGraphId

It seems that many other people have asked this question. I got a possible answer on the Power BI Issues forum saying that the userGraphID is the users Object ID that you can find in azure Active Directory – Users.

So I went into Azure – Azure Active Directory – Users and took my Object ID

I added the Object ID and got the results back that I was hoping for.

Great. This gives all the artifacts that I own. Can I now do this in Powershell like the previous API? Open Powershell again.

powershell_ise.exe

Can I reuse the Previous Script.

Open my previous Script. InvokePowerBIRESTMethos.ps1

 Param(
    #commented out the other API
    #[string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/groups?           %24top=10&%24expand=users%2Creports%2Cdashboards%2Cdatasets' 

    [string]$url = 'https://api.powerbi.com/v1.0/myorg/admin/users/objectID/artifactAccess'

   )

login-PowerBI


$result = Invoke-PowerBIRestMethod -Url $url -Method Get 

$workspaceContents = $result | convertFrom-Json

$firstworkspace = $workspacecontents.value[1]
$firstworkspace 

And this works. I get back all my objects

So this is just the initial investigation. To do

  • How to Delegate Permissions for this API?
  • How to Automate the Powershell script and create a dataset to work with?

And even further research

  • can we automate checking between a list of users from Yesterday and today to see who has gone to automate the process even further?
  • Is it possible that Azure can handle use cases where we know the user is leaving and have assigned a leave date in Azure Active Directory?
  • Then Feed this loop into the API to generate this list so we know what reports need to have their ownership taken over
  • Automate the handling of take over accounts

Tabular Editor and the Best Practice Analyser for Power BI

Finally I get a chance to have a look at the Best Practice Analyse in Tabular Editor

https://powerbi.microsoft.com/en-ca/blog/best-practice-rules-to-improve-your-models-performance/

I am so excited about this because we are looking at ways to improve and speed up the reviewing process on all the datasets being created.

Currently our Power BI Power Users go in to review and take a lot of time checking out the models, processing time, DAX Measures, etc etc.

This is great but if there are ways to speed up that process so we can review even more models then I am in.

So lets get started.

DAX Studio

I always go to DAX Studio connected to my model to get a look at the model Summary before I do any updates. Advanced Tab – View Metrics

Tabular Editor

With a dataset open in Power BI Desktop I open up Tabular Editor from External Tools

And then go to Advanced Scripting

And now I am ready to load in the Best Practice Rules from GitHub.

https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules

I go for the Automated Set up approach and run the following code

I believe that these rules get updated so when you run it again your rules will get updated.

Then after running the code I create a custom action over the code by clicking the green + Button

And you now have it in Samples

I can then Close and Open tabular Editor again. (I am doing it with a model already open but you can open it separately and connect to a model.

Then I go to Tools and Best Practice Analyser

And here is where it gets interesting. It brings up a list of Best practice rules that have been worked on by Microsoft experts for a long time. I really want to get a good look at where they are at at 15/10/2021

At this point its fair to say that this has been incredibly easy to do and very useful so far.

Of course the rules come up very specific to my model. 172 objects are in violation of 17 best practice rules. Lets have a look at some of them

[Performance] Do not use Floating Point data Type

My Latitude and Longitude are in Violation here so I should be going back to the model and checking that I cant change these items and not get issues

Some fixes can be taken from best practice analyser.

In this case I am selecting generate fix Script

And pasting to Advanced Scripting window to have a look at it

I can run this and it will immediately update my data model. However it should be said that Tabular Editor requires the XMLA endpoint to allow both read/write access. This setting is controlled by your capacity admin.

In the Power BI Admin Portal

If you don’t have this enabled you need to check with your Admin. At worst you can gather all these recommendations and implement them manually in Power BI.

If you can use XMLA endpoints you can also simply choose apply fix but I prefer to see the fix before implementing.

[Performance] Set isAvailableInMDX to false on None-Attribute columns (6 Objects)

I wasn’t aware of this and all my Keys within the dimensions are flagged as issues here. Lets have a look at that in more detail.

This is rather more specific to Analysis Services. You can stop attribute hierarchies from being build on columns that don’t need them.

Basically all attributes have this set to on. It allows a column on a table to be used on a row or column axis of a visual so it can for example filter a measure. the important thing to note here is that these are structures only used queries tabular models (Analysis Services) IN MDX. Power BI always runs DAX queries so its not really specific to out Power BI Model. We can ignore this rule

And hit Show ignored if you want them back

[DAX Expressions] No two measures should have the same definitions

I have two measures in my DAX. I can right click on each to go to object. and I can then look at the expression

CALCULATE(DISTINCTCOUNT(fact[AccountID]),
FILTER(Accounts, Accounts[Name]<>"NA"))
CALCULATE(DISTINCTCOUNT(fact[AccountID]),
FILTER(Accounts, Accounts[Name]<>"NA"))

It goes to show that these things do happen. You can’t apply a fix script here. Its a case of finding out what uses these two items and ensuring we only use one. So the Question is here, is there an easy way of answering this question?

[DAX Expressions] Use the DIVIDE Function for Division

5 of my objects aren’t using the DIVIDE Function created especially for this. I clearly need to remember that I have a habit of doing this.

There is no fix script for this but I can go to object and to the Expression Editor

I have recreated as a proper DAX Function and you can simply hit the tick to update the code in your model.

[DAX Expressions] Filter Column Values with the Correct Syntax

I am unsure what this means so go to Manage Best Practice Rules in the top corner of the screen

Hit Edit Rule

And you can get to a description of the rule

Instead of using this pattern FILTER(‘Table’,’Table'[Column]=”Value”) for the filter parameters of a CALCULATE or CALCULATETABLE function, use one of the options below. As far as whether to use the KEEPFILTERS function, see the second reference link below.

Option 1: KEEPFILTERS(‘Table'[Column]=”Value”)
Option 2: ‘Table'[Column]=”Value”

Reference: https://docs.microsoft.com/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument
Reference: https://www.sqlbi.com/articles/using-keepfilters-in-dax/

This is fantastic. I can even go to the document reference to get more information. When I go to the object I can see that I have done this

CALCULATE([Number of Records],FILTER(Records, RecordType[Type]="Test"))

And immediately its clearly to me. CALCULATE is supposed to be fast for one implicit Filter. you use FILTER when its a little more complex and is slower. Although this works I have absolutely no need to use the filter.

CALCULATE([Number of Records],Records[Type]="Test")

And there you go. A faster bit of DAX. Current opinion of this tool. I am really quite taken with this. Its a bit of a gamechanger.

[Maintenance] Visible objects with no description

I have lots of these and absolutely. Each one should have a good description against it. I hadn’t even thought of that.

There are quite a few more rules that I have violated. I wont go through them all here but I am really excited to see how I can do so much more to streamline my model. this is absolutely fantastic.

Additional Script Rules

Note that there are several rules which require running an additional script. And these steps are specific to the Vertipaq Analyser which was added to DAX Studio. We can use the Vertipaq analyser combined with the best practice analyser

https://www.elegantbi.com/post/vertipaqintabulareditor

After reading the documentation above I took the script and ran it in Advanced Editor. I also saved it as a sample just in case.

This should create Annotations that you can see in Tabular Editor when you look at the objects but I cant see anything in my model. this was because you need to go to File – Preferences and Allow Unsupported Power BI features.

Now I can see Annotations

We are basically saying here that in Power BI desktop you can’t create Annotations. This can only be done within the external tool so there is a possibility that this could cause issues. From the documentation it does seem that this is fine to do, but you could now do other things that could cause problems in future. One to think about.

So, We have run report that basically creates the same stats as the Vertipaq Analyser in DAX Studio. How do we use it with Best Practice Analyser?

Avoid bi-directional relationships against high-cardinality columns

We need to create the rule over the script

Add a New rule. I’m simply Following the documentation at this point so lets get to the end of the rule creation

Its important to set the Applies to

If you don’t specify the Applies to you will get an error message (See image below)

I save the new Rule into the Rules for the Local user. But a question here is that, If I rerun the Advanced Scripting Code Sample again to get any new changes, will this wipe of the item created?

To test I go back to Power BI and Change a join to bi directional

And then go back to Tabular Editor and Update the Change Rerun the Best Practice Analyser and I am shown my one bi directional join as a best practice violation. this allows the user to say if its valid

Or if I should do something about it. I will do this in Power BI and rerun

Large tables should be partitioned

When you run the script for Avoid Bi Directional Relationships you also Create the Vertipaq rule for this best practice.

Frustratingly the documentation gets a little confusing at this point. the only rule that has a screen shot to show how to create it is Bi Directional functionality.

I’m making the assumption then that you don’t need to set up the rule and It should automatically appear. However my model isn’t big enough for this to be violated.

Is it a rule then? Got to ToolsManage PBA Rules .

Yes, there it is, I’m just not violating it. I was asked to look at this documentation for more information at this point but I am finding it a little confusing.

https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules

Reduce usage of long-length columns with high cardinality

Again for this one, When you run the script for Avoid Bi Directional Relationships you also Create the Vertipaq rule for this best practice.

But you also have to run another script (again I saved as a sample)

Is this a rule then? Got to ToolsManage PBA Rules

This is good, the rule is there but I am not causing any violations to happen

Split date and time

In the above gitHub information you are told to run another script for this which I saved as a sample.

Best Practice Analyser was run again and it appears as a violation. However it is erroring

I know Split date and time is incredibly important to keep the models as speedy as possible so I would like to resolve this problem.

Rerunning the Scripts

Its been some time and we want to review another model. We also think it might be a good idea to rerun the original script in case the rules have changed.

Open another model in Desktop. then open Tabular Editor. Because I have enabled experimental Power BI features I get this warning.

Because we have already run the code we can simply open up best practice analyser to get the results. But what if we want to update for possible changes?

It would actually be good to have a way to know if there have been changes made to the rules?

Rerun the script. close and reopen like last time

Tools – Manage BPA Rules – Rules for the Local User

The additional rules are still there which is great. Lets run the analyser. 367 objects are in violation of 22 best practice rules.

Lots more to do then and all to make the model better and more efficient.

Conclusion

The First section of this just works and I’m incredibly pleased with it and I think it will really help.

The Vertipaq Analyser against Best Practice Analyser references are more difficult to follow but it seems to be that there are three extra scripts to run and one of the rules actually needs to be set up. the other rules are automatically created.

Obviously you have to allow for XMLA Endpoints in order to update your model using Tabular Editor so there are some things to think about here.

Having to set Allow Unsupported Power BI features is a worry for the Vertipac Analyser rules. Having gone through the process to try and figure out how it would sit in our review process I think you could do the following

Power BI Premium Deployment Pipelines and Sub Workspaces – Publishing a live report to a pro workspace from a premium workspace

I wanted to have a look at a specific set up we have in regards to our Power BI Workspaces and apps

  1. We have Power BI Premium P1 Node
  2. We use dataflows
  3. We use a Deployment Pipeline for Dev Test and Production Premium Workspaces
  4. We have a scenario where we have a sub workspace with a smaller number of reports than the main reports

Deployment Pipelines

Lets have a look at the master Workspace that contains all our dataflows, datasets and reports live connected to the dataset

And for all three areas there is also an app to test usability for Viewers (and for the viewers of all the reports in Prod

Current issues with Deployment Pipelines and dataflows

The great thing about pipelines is that we don’t have to keep copies of Test and Prod reports. There are just Dev Pbix files. However, for Production. How do you know that your Production reports are using the dataflows connected to the Production SQL database?

The issue at the moment is that there is a bug that leaves the development data source in with the Production data source for the data flow. You can see this if you go into Lineage View

In the Prod Pipeline we have set the reports to use Prod dataflow and updated the user and password but from here how do you know that this has worked?

The best way I have found to do this is to export the dataflow Json … Export to Json

And look at the file in Notepad and check the source Source = Sql.Database(\”prd-

Its showing as production so I can be happy that the production reports are against the production database

The Sub Workspace

So we have a model for a production app to sit on where users can view all the reports.

However we have another cohort who can only see the reports B and not report A or C.

We can only have one app per workspace so we cant create another app with just one report.

The answer here is to create a sub workspace and republish the Production report across to this new workspace.

The question here is….. Can the sub workspace be a none premium workspace if we are live connected to a dataset in a Premium workspace?

Lets find out by first assessing the situation we want to set up

Process to move the production reports in the deployment pipeline to another workspace

We want to publish the production report to a new workspace (With just the one report in it)

And as a further complication, We want to live connect to a dataset in a Premium workspace and recreate a report in a Pro Workspace.

Not only that, but the Pbix file is set to dev not Prod and there is no production pbix file.

First of all in your Production Workspace go to your report (content) Click … and Save a copy

And Here is the Lineage in Power BI

So how do we check that this report is connected to Dev data flow not prod dataflow

At this point I would probably recommend having something in SQL to connect to like a view or a table containing the datasource name to test with. This would be really handy.

We have to assume that this is the case because it comes from Production workspace which is definitely connected to Prod its good. Also, You could check the data between dev and Prod at this point too.

To test, an app was published and the data was exactly the same as the report in the master workspace.

The good thing about this set up, you only have one copy of the data in master. You don’t have to have the same data duplicated in another workspace, taking up more space in the Power BI Premium Node.

And because its such a small workspace, we don’t have to create it in Premium so we don’t need our Power BI Admin to set it up as a Premium workspace

Licensing

Now you have to think about Licensing. Everyone who needs access to the report in the Pro workspace now needs a Pro account themselves. Great if you have a P3 License which gives everyone free Pro accounts.

But what if you don’t have everyone on Power BI Pro licenses. Or you have external users and you haven’t given them a Pro license?

Lots to think about here with this Mix of Premium and Pro. this might then be a case of going to your Power BI Admin and asking them to switch the Workspace to Premium simply due to the Licenses against the report viewers.

Power BI Can you use an inactive join for a slicer (USERELATIONSHIP)

Imagine the scenario. You are dealing with two dates. date A and B. You create an active and an inactive relationship to your date table.

You can create Dax with USERELATIONSHIP for any measures that use the inactive join. Great for the bar charts, line charts etc.

But what if you have a slicer for Year on the report? At the moment that slicer is connected to Date A because you simple drag year from the date table.

This report needs to be sliced against Date B. Is there a way to do this?

Lets look at one of our measures that uses USERELATIONSHIP

YTD Measure =

CALCULATE(TOTALYTD([Number of Records],’Date'[Date]), USERELATIONSHIP(Metrics[DateKeyB],’Date'[DateKey]))

The USERELATIONSHIP can only be used with CALCULATE,CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD 

From this it is clear you can only use it in a measure with a metric.

And you cannot use a Measure in a slicer. Slicers have to be created from a column.

Which means that our inactive dates can’t be used in a slicer. And this is problematic because if you have a page of time based visuals all using the USERELATIONSHIP then its a fair shout that you are going to have a slicer too for example ‘Year’

So can we come up with a workaround?

Workaround 1. Adding date information to the fact table

Out model rules are

  • DateKeys in the Fact table. I also hold the dates hidden just in case
  • The dateKey Joins to the date Dimension which contains Year, Day, Month etc

So what I am going to do in this case is add my relationship B details into the fact table just for the time being.

s.[DateB] AS [Date B],
--Workaround 1
DATEPART(Year,s.[DateB]) AS [Year B],
DATENAME(Month,s.[DateB]) AS [Month B],
DATEPart(Month,s.[DateB]) AS [Month No B],
[staging].[fnQuarter](s.[DateB]) AS [Quarter B],

I have created a Quarter function with the specific quarter logic which is use for every Quarter period.

This is then refreshed into the Power BI Dataflow

And then refreshed into my data set.

All ready to work with. What I decided to do in Power BI was create a table containing those values.

Support End Date (For Slicers) = 
SUMMARIZE ( 'GrowthHubMetrics', 
GrowthHubMetrics[SupportDateKey],
'GrowthHubMetrics'[Support End Quarter], 
'GrowthHubMetrics'[Support End Month], 
'GrowthHubMetrics'[Support End Month No], 
'GrowthHubMetrics'[Support End Year], 
'GrowthHubMetrics'[Support End Date])

The dates in the fact table were then hidden leaving the above Slicer table.

And then I remembered that Date time Measures don’t work without a continuous date table and DateB isn’t continuous.

But then I remembered that I had already put in a lot of work creating all the date time measures on the inactive join to the date table using USERELATIONSHIP so actually all the date time measure are fine.

Would I go back and create a date table for dateA and a date table for DateB so I can have slicers and drill down to the date. Its very possible but that renders the use of USERELATIONSHIP Pretty null and void, unless you still want to slice these visuals against DateA.

Connect the DAX table to the model.

the date Table for Slicers is connected up to Dimdate in the model and we are ready to go

So all the measures are still connected to the date table through the inactive join

But the slicer can be set up over the new DAX table

I’m hoping to get a better way of doing this as we go because it seems that you should be able to do this without having to create another table.

Update

In the end, this became so complex and issue heavy (Drill throughs not working properly etc) I gave up. Created a reference of the date table for Date B. Connected this up. removed the DAX table and changed all my measures to get rid of USERRELATIONSHIP.

So Pages 1 to2 use DateA and Pages 3 and 4 use DATEB.

As a conclusion, if you are wanting to use Drill through and Drill down I think I would recommend role playing dimensions. the complexity of the USERELATIONSHIP with just seems incredibly complex at the moment.

Its a shame because I love the idea of switching to a none active join but in practice its proved unworkable.

If you have any tips on this please let me know

Power BI. Created Reports in your ‘shared dataset file’ and now want to move them to a report pbix file. How do you go about this task?

The recommended approach to Power BI as an enterprise solution is to have your ‘shared data set in a pbix file where you can just create test visuals to check everything is ok.

Then create the real reports in a separate pbix file live connected to the dataset.

But what happens if you create great reports, including Bookmarks etc in the shared data set before deciding to move everything?

Changing the data source

Instead of creating a new pbix file and copying all the visuals in (this wont really work if you have bookmarks and more complex reporting set up)

First, save your ‘Shared pbix’ file with another name which will becomes the reporting pbix file. The original pbix file complete with the original reports can be left as is.

You could change the data sources over to the ones in the data set. However this creates an issue.

The Data set pbix file sits on dataflows.

If you currently try and change to data sets, Data sets aren’t offered.

So what do you do?

Remove Queries

In this example all the M Code for the Query should be done in the Dataflow within service. the only M Code in the shared data set should be the connection to the data flow.

All the Queries should be deleted. If you make sure your dataflow queries have the name you want in the dataflow, Re adding them back will be fairly straight forward

Close and Apply your Query

Back in Power BI all your visuals show as having issues. There is no data.

Go to Get Data and now you can choose datasets

Find the data set and select. This should then load in Live data from the data set and your visuals will be displayed again.

Publish this back up to service.

At this point you can look at Lineage to view that you are using the power BI recommended approach

The App

When I create the app for Viewing I simply hide the Dev reports from the users.

This appears to be the speediest way of moving your reports to their own file, if you have already done a lot of reporting work in the ‘shared data set’

Create your website with WordPress.com
Get started