PowerBI embedded in Tier1 and Tier2 environments

What is the proper way to access the data tables for Power BI in Production, Sandbox, etc?  

If you need to connect PowerBI to Microsoft Dynamics 365 for Finance and Operations you have several options depends on the environment that you are using.

Tier2 (PROD/UAT/TEST) or Tier1 (developer onebox)

• Tier2 environments have Azure SQL AXDW database which can be configured for automatic updates using System Administration > Entity Store > Refresh all cubes/measures. In that form you can setup update recurrence(some cubes you can update once a day, some once an hour). To access data in Tier2 you can use Direct Query. Direct Query connection to the cubes (AXDW database) is done through  Azure SQL database

Note: ODATA has access only to AXDB and does not have access to cubes, only to AXDB data.

• Tier1 environments have only SQL databases and that is real headache to configure PowerBI embedded for onebox environment. It requires external AXDW configured in Azure. 

How to create KPI measure in Power BI

If you want to build KPI visuals as it is presented on the screen below, you can achieve this by adding new measure and use the following DAX expression

KPI =

SWITCH (

TRUE (),

[Profit/Loss] > 0, unichar(9650),

[Profit/Loss] < 0, unichar(9660)

)

If Profit/Loss value is negative, you will have black down-pointing small triangle. If Profit/Loss is positive, you will have black up-pointing small triangle.

If you want to have different colors based on the values, you may use Conditional formatting as it is presented below.

How to apply Regional settings from the Source data formats to the exported data through Data management

As you might know, there is a possibility to setup Regional settings on the Source data format in the Data management. 

Unfortunately those settings are not applied automatically. The important thing to make it work is the following:

When create Export project and add entity to the project, set Skip staging toggle to NO. Only in this case, Regional settings will work.

How to use grouping in Power BI and apply group to the DAX expression

 Let's say you have field that contains the fixed list of values and you want to group them. In my scenario I have TypeID field that contains the following values: Revenue, Liability, Equity, ProfitandLoss, Expense, BalanceSheet, Asset, Total. In my case, Revenue, Liability, Equity corresponds to Credit and the rest to Debit. What can I do? 

1. Highlight the TypeID, right-click and choose New group
2. Set the Group name (TYPEIDGROUP for example)
3. Select Revenue, Liability, Equity values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Credit name
4. Select remaining values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Debit name. 
5. The result should be as presented on the screen

Now, you are able to connect to the group and use it in the formula. For example, I used it in the created measure:

Actuals credits positive =

CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],

FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Debit" )

- CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],

FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Credit")

Another option is to use IN expression

Labor =

CALCULATE

(

    FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actual operating]*-1,

FinancialReportingTransactionData_MainAccount[VALUE] in {"600300", "600400"}

)

It's up to you to decide which approach to use.  

How to find and get quarter / quarter name (month / month name) from a given date in Power BI

Let's imagine that we have General ledger attributes table that contains Date field

You would like to extract Years, Quarters, Months values from the Date field. For this purpose you can create new columns (Month, Quarter and Year) and use the following DAX expressions:

Month = MONTH('General ledger attributes'[Date])

Quarter = QUARTER('General ledger attributes'[Date])

Year = Year('General ledger attributes'[Date])

The issue you might experience is that Month column will return numeric values instead of text (like 1,2,3,4,....12). Quarter column will return numeric values also (like 1,2,3,4).

What if you want to have text values. For Quarters, for instance, to have Q1, Q2, Q3 and Q4. For Months, to have January, February etc.

You may try to use the following expression:

FORMAT('General ledger attributes'[Date], "MMM")

If it doesn't work as it was in my case, because of the following error message

Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models.

you can create additional columns and use the following expressions:

MonthName =

SWITCH('General ledger attributes'[Month], 1,"January", 2,"February",3,"March",4,"April",

5,"May",6,"June",7,"Jul",8,"August",9,"September",10,"October",11,"November",12,"December")

QuarterName = SWITCH('General ledger attributes'[Quarter], 1,"Q1", 02,"Q2",3,"Q3",4,"Q4")

The structure of the columns will be the following:

Now you can build visualizations using new created columns.

Batch Management Service cannot be started

If you experience the following issue while starting Microsoft Dynamics 365 Unified Operations: Batch Management Service:

Windows could not start the Microsoft Dynamics 365 for Operations - Batch Management Service service on Local Computer.

Error 1064:An Exception occurred in the service when handling the control request.

The reason might be because your environment is set to Maintenance mode.

To change this, execute the following script:

update SQLSYSTEMVARIABLES SET VALUE = 0 where PARM = 'CONFIGURATIONMODE'

Afterwards, do not forget to restart IIS by running the following command:

C:\windows\system32> iisreset

How to turn on maintenance mode through SQL in Microsoft Dynamics 365 F&SCM

In order to turn on maintenance mode in Microsoft Dynamics 365 F&SCM through SQL, the following steps needs to be performed:

1. Open Microsoft SQL Server Management Studio 
2. Connect to the Server
3. Expand AxDB database and execute the following script

update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'

       See the result of the update:

select * from SQLSYSTEMVARIABLES

    After update, execute iisreset command in cmd. 

    To return back execute the following query:

update SQLSYSTEMVARIABLES SET VALUE = 0 where PARM = 'CONFIGURATIONMODE'