Product:
Microsoft Power BI portal / service

Issue:

How move a dataflow gen1 from one workspace to a another workspace?

Solution:

From source workspace, click on the 3 dots … and select export json file.

Save the file on your hard disk.

Change the powerbi workspace to the new workspace where you want to import the dataflow

Click on “new item” and select Dataflow Gen1

Click on “import model”

Select the json file you have saved – and click open.

When the dataflow is imported, you need to update the credentials to connect to the data source.

Click on the 3 dots … and select edit.

Click on the edit table icon for one of the tables in the list.

You may get a message like: Credentials are required to connect to the SQL source.

Click on options.

If you use a power bi query gateway to access your SQL database, you need to select it at data load, and click OK.

Click Save and close.

Now it should work to update (refresh) your dataflow.

If it does not help, you may need to go to manage connections, and try to set a different user to access the data source.

More Information:

https://forum.enterprisedna.co/t/moving-data-flows-and-report-to-a-new-workspace/22166 

Move dataflows across workspaces with the Power BI REST API

https://www.phdata.io/blog/migrating-queries-from-power-bi-desktop-to-dataflows/ 

Product:
Microsoft PowerBI desktop

Issue:

A column in a table is not sorted correct, it sorted alphabetical and not by date. Even do the column should show a date format.

Solution:

Inside PowerBI Desktop, go to the table view.

Select the column (that you want change the sort order for) and click on icon for “sort by column”.

In the dropdown menu, select the column who have a correct format, in our example the date column.

Now check the table in Report view.

You need to repeat this for all columns, in your report in PowerBI for Desktop.

More Information:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

https://www.techrepublic.com/article/how-to-sort-by-column-power-bi/ 

https://help.zebrabi.com/kb/power-bi/add-a-sorting-column/ 

Product:

Microsoft Power BI desktop
Microsoft Windows

Issue:

Error when you try to add a measure in the visualizations list of data.

Solution:

Try to add a new column instead. The icons tell what type of value it is.

More Information:

Another important difference between measures and calculated columns is that measures are evaluated in the filter context of the visual in which they are applied. The filter context is defined by the filters applied in the report such as row selection, column selection, report filters and slicers applied. Measures are only evaluated at the level of granularity they are plotted at. As calculated columns are computed when you first define them/ when you refresh your dataset, they do not have access to the filter context. Calculated columns are calculated outside of the filter context and do not depend on user interaction in the report.

When you write a calculated column, you need to rely only on the row context. The row context, is simply the notion of a current row. It specifies which row we are calculating the values for, so that DAX can look at other values in the same row. In a calculated column, the row context is implied. When you write an expression in a calculated column, the expression is evaluated for each row of the table. The calculated column has knowledge of the current row.

By contrast, measures implicitly do not have a row context. This is because, by default, they work at the aggregate level. So, you cannot refer to columns directly in a DAX measure, you will get an error because no row context exists. This is because the measure will not know which row to choose in the table

https://endjin.com/blog/2022/04/measures-vs-calculated-columns-in-dax

Measures vs. Calculated Columns in Power BI

https://www.thedataschool.com.au/mipadmin/the-differences-between-new-measure-and-new-column-in-power-bi/ 

https://biinsight.com/define-measure-table-power-bi-desktop/ 

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server
Google Chrome

Issue:
The background image in TM1WEB is changed on the server – but the change is not shown on end users web browsers.

Solution:

On many browsers, pressing Control-F5 will reload the page and bypass the cache. The Wikipedia “Bypass your cache” page has details for all modern web browsers.

Or you can change the name of the file PA_header.svg in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login to something else. Then you need to update all code that calls that file – can be this css-html files for tm1web:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\carbon.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\all.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\LoginDialog.css

Or clean the Web Browser cache at the end users laptop.

More Information:

https://medium.com/@gulshan_n/overcoming-persistent-image-caching-how-to-ensure-visitors-see-your-latest-website-changes-cd89a1434e1a 

https://www.avast.com/c-how-to-clear-cache

Resurrecting images from a web browser cache can be a useful way to recover lost or deleted images that you previously viewed online. Here’s a step-by-step guide on how to do this for various web browsers:

Google Chrome

1. Access Cache:
   – Type chrome://cache in the address bar and press Enter. This will take you to a list of cached files.
2. Find Images:
   – You can’t view the images directly, but you can see the URLs. To find images, you can also use a tool like ChromeCacheView (a third-party utility) that allows you to view and extract images from the cache more easily.
3. Extract Images:
   – If using ChromeCacheView, download and run it. It will display cached files, including images. You can select the images you want to save and extract them to your computer.

Mozilla Firefox

1. Access Cache:
   – Type about:cache in the address bar and press Enter. This will show you the disk and memory cache information.
2. Find Images:
   – Look for the section that lists the cache entries. You can find images by checking the file type and URL.
3. Extract Images:
   – You can use Mozilla’s cache viewer or a third-party tool like Firefox Cache Viewer to extract images more conveniently.

Microsoft Edge

1. Access Cache:
   – Type edge://cache in the address bar and press Enter. This will show you cached items.
2. Find Images:
   – Similar to Chrome, you might need a third-party tool like EdgeCacheView to find and extract images.

Safari

1. Access Cache:
   – For Safari, the cache is not easily accessible like in other browsers. You can use Terminal commands or third-party tools.
2. Find Images:
   – You can also look in the ~/Library/Caches/com.apple.Safari/ directory to find cached files.
3. Extract Images:
   – Use a third-party application like SafariCache to help retrieve images from the cache.

General Tips

• Look for File Types: When browsing the cache, look specifically for file types like .jpg, .png, or .gif.
• Use Third-Party Tools: Tools like WebCacheImage, ChromeCacheView, and others can simplify the process.
• Browser Extensions: Some extensions can help manage and view cached files directly from the browser.

https://www.linkedin.com/pulse/11-ways-fix-google-chrome-loading-images-benjamin-ladegbaye 

How to refresh cached images and files in Chrome

Product:
Microsoft SQL server

Issue:

In a view of dates, we only want the 5 last years and the 5 future years, from today date. How do it?

Solution:

Create a view of the table listed above, where you define it like below:

CREATE VIEW [DM].[DMDateView]
AS
SELECT [key_dimDate]
,[DateIndex]
,[Date]
,[Day]
,[DaySuffix]
,[Weekday]
,[WeekDayName]
,[DOWInMonth]
,[DayOfYear]
,[WeekOfMonth]
,[WeekOfYear]
,[ISOWeekOfYear]
,[Month]
,[MonthName]
,[Quarter]
,[QuarterName]
,[Year]
,[MMYYYY]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[FirstDayOfNextMonth]
,[FirstDayOfNextYear]
FROM [DM].[dimDate]
where 1=1
and [Date] BETWEEN DateAdd(yy, -5, GetDate()) AND DateAdd(yy, +5,GetDate())

More Information:

https://www.mickpatterson.com.au/blog/add-auto-increment-to-a-column-in-sql-server

https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

https://www.dbvis.com/thetable/sql-server-dateadd-the-complete-guide/

Product:

Microsoft SQL server

Issue:

How check if a column can be converted to numeric from string?

Solution:

Start SSMS and enter below SQL code to list rows that is not convertable to numbers:

 select TOP (1000) * FROM [schemaname].[tablename]
where ISNUMERIC (columname) = 0

Below code to make a full copy of a table:

SELECT *
INTO newtable 
FROM oldtable
WHERE condition;

Below code to only list rows where the column content is above 15 charcters:

 select TOP (1000) * FROM [schemaname].[tablename]
where LEN (columnname) >= 15

More Information:

https://www.w3schools.com/sql/sql_select_into.asp

https://www.airops.com/sql-guide/how-to-check-length-string-sql

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16 

Product:

Microsoft SQL server

Issue:

How update a value in a column in a existing table – for example divide with 1000?

Solution:

UPDATE table_name
SET value1 = value1 / 1000
WHERE condition ;

Keep in mind to check the key dim table, so you include the correct rows in the update.

  The key value for the version need to be collected from the version table.

The account value you want to update in fact table may have more than one key value, that you need to check and include.

In the fact table you can use the update command, but you need to carefully test the where clause so you update the correct rows.

Use below code to update the column value where the account is the defined value

 UPDATE [DM].[fact]
SET value = value / 1000
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

Use below code to get a sum of column value:

 SELECT
Sum(value)
FROM [DM].[fact]
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

This can be written easier with a SQL JOIN between the tables.

UPDATE a
SET a.value= a.value / 1000
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto]
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

To see the sum

SELECT SUM (a.value)
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto] 
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

More Information:

https://www.w3schools.com/sql/sql_update.asp 

https://learnsql.com/blog/sql-division-operator/

https://www.w3schools.com/sql/sql_join.asp

https://www.sqlshack.com/learn-sql-join-multiple-tables/ 

https://www.geeksforgeeks.org/sql-update-with-join/ 

https://www.datacamp.com/tutorial/sql-update-with-join

https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/ 

https://www.javatpoint.com/sql-update-with-join

Product:

Microsoft Excel

Issue:

You have a list of values (maybe dimensions) in column A and you want to find if they exist in column C. How do it in Excel?

Solution:

Mark column C, and enter the name “searcharea” in the top left name area (called the name box) , press enter. Now you have created a “range”.

Go to cell D1.

Enter Formula = COUNTIF ( searcharea; A1 )

Mark cell D1, and with the mouse drag down all the way to the last cell.

The formula should update automatic, if you do right, to in cell D2 be = COUNTIF ( searcharea; A2 )

This should give a 1 in the rows where the value in cell A exist in the C column.

More Information:

https://www.wps.com/academy/check-if-value-is-in-list-in-excel-(3-easy-methods)-quick-tutorials-1865235/ 

https://www.exceltip.com/lookup-formulas/check-if-value-is-in-list-in-excel.html

https://excel-tutorial.com/why-excel-formulas-use-comma-or-semi-colon/ 

Force Excel to use commas (,) instead of semicolons (;) in separating formulas

https://exceljet.net/glossary/list-separator 

https://exceljet.net/glossary/named-range 

Product:
Microsoft Power BI Desktop

Issue:

How do in Power BI connect a card (that show a single number) to only a few selector widgets (slicers) on a page?

Solution:

To connect a card to a selection widget (like a slicer) in Power BI Desktop without affecting other visuals on the same page, you can use the Edit Interactions feature.

Create Your Card and Slicer:

First, ensure you have both the card visual and the slicer visual on your report page.
Select the Slicer:

Click on the slicer visual to select it.
Edit Interactions:

Go to the Format tab in the ribbon.
Click on Edit Interactions. This will show interaction controls on all other visuals on the page.
Set Interaction for the Card:

On the card visual, you will see two icons: a filter icon and a no filter icon.
Click the filter icon to ensure the slicer affects the card.
For other visuals that you don’t want to be affected by the slicer, click the no filter icon.
Turn Off Edit Interactions:

Once you’ve set the interactions as desired, click Edit Interactions again to turn off the interaction mode.
This way, the slicer will only filter the card visual and not the other visuals on the same page

More Information:

You can get good information from the different CHAT AI tools today about Microsoft Products, try:

https://copilot.microsoft.com/chats 

https://openai.com/chatgpt/overview/

https://www.getmerlin.in/ask-ai

Product:

Microsoft Power BI portal

Issue:

When refresh a powerbi report ( e.g. Semantic model ) we got a error – this report worked before in the Power BI portal workspace. Error is like:

Data source error{“error”:{“code”:”DMTS_OAuthTokenRefreshFailedError”,”pbi.error”:{“code”:”DMTS_OAuthTokenRefreshFailedError”,”details”:[{“code”:”DM_ErrorDetailNameCode_UnderlyingErrorMessage”,”detail”:{“type”:1,”value”:” Device is not in required device state: compliant. Conditional Access policy requires a compliant device, and the device is not compliant. The user must enroll their device with an approved MDM provider like Intune…

Solution:

You must be in Microsoft Edge web browser to do this change, if you do the change in Chrome, it will not work.

On your reports Semantic model – click on the 3 dots and select “settings”.

Go to data source credentials and click on edit credntials that is marked as not working.

And enter your windows account credentials again.

Now it should be green, and this login is affecting all your reports – if the access point is the same.

Now click the “refresh” icon to update you PowerBi report semantic model in the portal.

If above does not work, try below.

Download the Semantic model to you computer.

Restart your computer.

Login to your company windows account.

Open the PowerBI pbix file, and refresh the report.

You will be prompted to login to the datasource.

Enter your credintials for windows account.

When the report is refreshed and working in PowerBI on your computer.

Save the report.

Publish the report to the powerbi portal – overwrite the previus report.

More Information:

https://learn.microsoft.com/en-us/entra/identity/conditional-access/concept-conditional-access-conditions

Blood, Sweat, and built-in compliance policy

There is a known issue with the Chrome browser that can cause this error to occur. If there is no device information sent in the sign-in logs, this might be the problem. Device information is sent when there is a PRT and the user is logged onto the browser. If the user is using Chrome, the Windows 10 accounts extension is needed.

If this is the case, you can test by asking the user to logon to the Edge browser or install the Windows 10 accounts extension to see if the issue is resolved.

https://learn.microsoft.com/en-us/azure/active-directory/conditional-access/concept-conditional-access-conditions

If they are signing in using Edge, they cannot use an incognito window because it will not pass the device state.

Is Microsoft Authenticator App is installed on the device? As Microsoft Authenticator is an broker app for iOS and would be needed to pass MFA and Device claims to Azure AD.

Sign-ins from legacy authentication clients also do not pass device state information to Azure AD.

Some years ago Microsoft stated that the in-app browser must be using a supported browser such as Edge, however, the Windows store uses Edge, and it also does not pass the device ID, so the conditional access policy can’t be compliant.

Many 3rd party applications use in-app browsers that are “not supported,” and it appears that Microsoft doesn’t offer the appropriate developer documentation that would allow 3rd parties to include this conditional access device information in their in-app browsers, even if they used Edge.

These are the links that were provided as dev resources:

• Developer guidance for Azure Active Directory Conditional Access – Microsoft identity platform | Microsoft Docs
• How to enable cross-app SSO on Android using MSAL – Microsoft identity platform | Microsoft Docs
• Configure SSO on macOS and iOS – Microsoft identity platform | Microsoft Docs

https://www.graber.cloud/en/aadsts50131-device-not-required-state/ 

https://cloudbrothers.info/entra-id-azure-ad-signin-errors/ 

You may need to do below in chrome to get it to work…

this is the exact requirements (Chome-side, your Azure AD setup has its own stuff) you needed:

Latest “Chrome Enterprise Policy List”: https://support.google.com/chrome/a/answer/187202?hl=en

GPO Settings
User Configuration\Policies\Administrative Templates\Google\Google Chrome\HTTP Authentication
-Kerberos delegation server whitelist
autologon.microsoftazuread-sso.com,aadg.windows.net.nsatc.net
-Authentication server Whitelist
autologon.microsoftazuread-sso.com,aadg.windows.net.nsatc.net
# Needed if you’re blocking extensions from being installed to whitelist this one
User Configuration\Policies\Administrative Templates\Google\Google Chrome\Extensions
-Configure the list of force-installed apps and extensions (Enabled)
ppnbnpeolgkicgegkbkbjmhlideopiji
-Configure extension installation allow list (Enabled)
ppnbnpeolgkicgegkbkbjmhlideopiji

Note: That extension ID I pulled from https://chrome.google.com/webstore/detail/windows-accounts/ppnbnpeolgkicgegkbkbjmhlideopiji (Windows Accounts)