What are the various User Roles available in Power BI Workspaces

Roles in the Power BI Workspaces
The Roles let us manage who can do what in the new workspaces, so teams can collaborate. New workspaces allow you to assign roles to individuals, and to user groups: security groups, Microsoft 365 groups, and distribution lists.
To grant access to a new workspace, assign those user groups or individuals to one of the workspace roles: 

1. Admin
2. Member
3. Contributor
4. Viewer

Everyone in a user group gets the role you've assigned. If someone is in several user groups, they get the highest level of permission provided by the roles they're assigned. If you nest user groups, all the contained users have permission. All of these capabilities, except viewing and interacting, require a Power BI Pro license.

Now we will discuss in detail about each Role and its permissions :

Viewer :

This is a very basic, read-only Role that will be assigned to End users to allows them to view content of the workspace. It provides access to view every report, dashboard and workbook in that workspace. User with this role won’t be able to use Analyze in Excel option on reports, and won’t be able to access datasets or dataflows.

The Viewers are generally the App users who access/view the Reports from the App, instead of  going to the Workspace. 

The Power BI Apps, creates a good separation between the DEV and USER environment.

Contributor :

The Contributor role is generally assigned for the developers in the workspace. This role provides the access not only to reports, dashboards, but also to the datasets and dataflows. Users with this role can Edit the content as well as deleting it. They can publish a report into the workspace, or remove it but they cannot Publish / Un-Publish the App.

The contributor role gives them access to do all their development work in the workspace, with the following capabilities :

• Publish a report into the workspace
• Edit the content in the workspace
• Delete the content in the workspace
• Access to all workspace objects: reports, dashboard, workbook, dataset, and dataflow
• Copy content, use Analyze in Excel and etc.

Member :

The Member role provides the all Contributor role’s capabilities, additionally it grants the ability to Publish an App, or Unpublish it, or Update the App. 

We can treat this role as the Deployment Role. With this role, we can publish the content of a workspace as an App for the end-users. The Member role is having one of the most important actions in the workspace like migrating the content from DEV to USER environment.

The Member role also can set the access for Member-level roles or underneath (Contributor, or Viewer) for the users in the workspace.

Also the Member role allows us to do the individual sharing of reports and dashboards in the Workspace.

Admin :

The admin role provides the full access to the workspace. In addition to having the capabilities of all the Member’s role actions, the Admin can add or remove the Admins to the workspace, can update or delete the workspace.

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------

How to use the USERRELATIONSHIP Function to activate In Active Relationship in Power BI

USERRELATIONSHIP Function to activate the In Active Relationship in Power BI

The USERRELATIONSHIP Function can be used as filter argument in CALCUALTE to activate the inactive relationship between two tables, for that calculation.
Syntax :
USERELATIONSHIP(<columnName1>,<columnName2>)

Remarks :
USERELATIONSHIP can only be used in functions that take a filter as an argument, for example: CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.

USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. For example, CALCULATE(SUM([SalesAmount]), USERELATIONSHIP(FactInternetSales[CustomerKey], DimCustomer[CustomerKey])) will return an error if row level security is defined for DimCustomer.

USERELATIONSHIP uses existing relationships in the model, identifying relationships by their ending point columns.

In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.

An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.

If multiple relationships are needed to join table A to table B in a calculation, each relationship must be indicated in a different USERELATIONSHIP function.

If CALCULATE expressions are nested, and more than one CALCULATE expression contains a USERELATIONSHIP function, then the innermost USERELATIONSHIP is the one that prevails in case of a conflict or ambiguity.

Up to 10 USERELATIONSHIP functions can be nested; however, your expression might have a deeper level of nesting, ie. the following sample expression is nested 3 levels deep but only 2 for USEREALTIONSHIP: =CALCULATE(CALCULATE( CALCULATE( &lt;anyExpression&gt;, USERELATIONSHIP( t1[colA], t2[colB])), t99[colZ]=999), USERELATIONSHIP( t1[colA], t2[colA])).

Scenario :

Suppose, we have the Data Model as follows, where we have two relationships between the tbl_Calendar and tbl_Sales tables.

Active Relationship : tbl_Calendar[Date_Id] to tbl_Sales[Order_Date]

In Active Relationship : tbl_Calendar[Date_Id] to tbl_Sales[Shipping_Date]

From above Model, we will calculate the Total Sales by Shipping Date, using the function USERRELATIONSHIP, which activates the relationship between tbl_Calendar[Date_id] and tbl_Sales[Shipping_Date].

Sales by ShipDate = CALCULATE(SUM(tbl_Sales[Gross_Sales]),

USERELATIONSHIP(tbl_Sales[Shipping_Date],tbl_Calendar[Date_Id]))

Result :

Thanks, Tamatam

How to Create Virtual Relationships using TREATAS Function in Power BI DAX

How to Pass or Propagate filters from a unrelated table column values as input using TREATAS Function in Power BI DAX
The TREATAS Function applies the result of a table expression as filters to columns from an unrelated table. It treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column.

It treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column. It creates a virtual relationship is a DAX pattern to transfers a filter context from a table to another, simulating the behavior of a physical relationship defined in the data model. This technique is useful whenever a relationship does not exist, or when it cannot be created because the relationship is not a one-to-many, or because it is defined by two or more columns.

Syntax :
TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )

Notes :

table_expression - An expression that results in a table.

column - One or more existing columns. It cannot be an expression.

Remarks :

The number of columns specified must match the number of columns in the table expression and be in the same order. If a value returned in the table expression does not exist in the column, it is ignored. 

For example, TREATAS({"Red", "Green", "Yellow"}, DimProduct[Color]) sets a filter on column DimProduct[Color] with three values "Red", "Green", and "Yellow". If "Yellow" does not exist in DimProduct[Color], the effective filter values would are "Red" and "Green".

This function is best for use when a relationship does not exist between the tables. it is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

If you have multiple relationships(in-active) between the tables involved, consider using USERELATIONSHIP function instead.

Scenario:

Suppose we have the Data Model as follows, where we have two unrelated tables (tbl_Region , tbl_Cost)

Example 1:

From the above Model, we can calculate(measure) the Sales per Region using the TREATAS Function as follows.

Sales per Region =
CALCULATE([Total Sales],
    TREATAS(VALUES(tbl_Region[Region_Id]),tbl_Sales[SalesRegion_Id])
    )

Note :

The TREATAS Function will create a Virtual relationship between the tables tbl_Sales and tbl_Region. Next it passes the values of the Region_id from unrelated table to the Sales table.

here, [Total Sales]=SUM(tbl_Sales[Net_Sales])

Result:

Example 2:

Now we will calculate(measure) the Total Cost from the unrelated table tbl_Cost , by matching the values of Year and MonthNum of both the tables (tbl_Calendar, tbl_Cost).

Total Cost =
CALCULATE( SUM(tbl_Cost[COGS]),

    TREATAS(
        SUMMARIZE(tbl_Calendar,tbl_Calendar[Year],tbl_Calendar[MonthNum]),

              tbl_Cost[Year],tbl_Cost[Month]

                )

        )

Result:

Example 3:

Now we will calculate(measure) the Sales for a specific Year, Quarter and Region. In this example, we are just passing values to 3 columns as in list using TREATAS Function.

2014Q1EuropeSales =
CALCULATE( SUM(tbl_Sales[Gross_Sales]),

    TREATAS( {(2014,"Q1-2014","EUROPE")},

            tbl_Calendar[Year],tbl_Calendar[FiscalQuarter],tbl_Region[Region_Name])
        )

Result:

Example 4:

In the following example, we will created a Table, using TREATAS and Summary functions, where Month, Year values exist in both the tables (tbl_Calendar, tbl_Cost).

TreatAsTable =
TREATAS(
    SUMMARIZE (tbl_Calendar, tbl_Calendar[Year], tbl_Calendar[MonthNum]),
            tbl_Cost[Year], tbl_Cost[Month]
            )

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to Create a Top N Sales Summary in Power BI DAX

Power BI DAX to Create a Top N Sales Summary Table
Suppose we have the Data Model as follows:

We have the sample Sales data by Year and Product as follows:

Scenario:
Now we want to calculate the Top 3 Sales by Product per year from the above data. Also, the Sales which does not falls in Top 3 needs to group and show in a new row as "Others".

This can be done using the following DAX Table Expression:

Top3Sales Summary = GENERATE(

SUMMARIZE( tbl_Sales, tbl_Calendar[FisalYear]),
VAR Top3Products=TOPN(3,
ADDCOLUMNS(VALUES(tbl_Products[Prod_Name]),"@Amt", [Total Sales]),[@Amt])
VAR Top3ProdRanked=ADDCOLUMNS(Top3Products,"@Pos",RANKX(Top3Products,[@Amt],,DESC))
VAR SalesAllProducts=[Total Sales]
VAR SalesTopThree=SUMX(Top3Products,[@Amt])
VAR SalesOthers=(SalesAllProducts-SalesTopThree)
VAR RowOthers={("Others",SalesOthers,4)}
VAR Result=UNION(Top3ProdRanked,RowOthers)

RETURN Result
)

Notes:

here, [Total Sales]=SUM(tbl_Sales[Net_Sales])

Output:

Suppose if you simply wants to find the Top3 Sales by Product, you can find using the below DAX Expression :

Top3 Sales by Prod = TOPN(3, SUMMARIZE(tbl_Sales,tbl_Calendar[FiscalQuarter],tbl_Products[Prod_Name],"@Sales",[Total Sales]),[@Sales],DESC)

Result:

Thanks, Tamatam

How to connect to CSV and Excel Files in SharePoint from Power BI

Power Query to Connect to CSV and Excel Files in SharePoint from Power BI
We can connect to the .CSV and .XLSX files using the below Power Query methods:

1) Source: SharePoint Files - SharePoint.Files Method:

Parameters:

sp_root_folder = https://sharepoint.com/sites/RootFolder/

sp_target_folder = Shared Documents/General/SubFolder/TargetFolder/

Excel File(.xlsx) :

let

    Source = SharePoint.Files(sp_root_folder, [ApiVersion = 15]),

    TargetFolder =
    Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),

    TargetFile = Table.SelectRows(TargetFolder, each ([Name] = "SourceFile.xlsx")),

    SelectContent = Table.SelectColumns(TargetFile,{"Content"}),

    Content = SelectContent{0}[Content],

    ImportXL = Excel.Workbook(Content){[Item= "Src_Data",Kind="Sheet"]}[Data],

    Promote_Headers = Table.PromoteHeaders(ImportXL, [PromoteAllScalars=true])

in

    Promote_Headers

Comma Delimited CSV File(.csv) :

let

    Source = SharePoint.Files(sp_root_folder, [ApiVersion = 15]),

    TargetFolder =
    Table.SelectRows(Source, each ([Folder Path] = sp_root_folder & sp_target_folder )),

    TargetFile = Table.SelectRows(TargetFolder, each ([Name] = "SourceFile.csv")),

    SelectContent = Table.SelectColumns(TargetFile,{"Content"}),

    Content = SelectContent{0}[Content],

    Import_CSV =
    Csv.Document(Content,[Delimiter=",", Columns=null, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),

    Promote_Headers = Table.PromoteHeaders(Import_CSV, [PromoteAllScalars=true])

in

    Promote_Headers

2) Source: OneDrive Files - Web Method:
Parameters:

OneDrive_File_Path = /* Please specify your exact file path like below */

https://sharepoint.com/personal/userid_domain/Documents/SourceFolder/SourceFile.xlsx

Excel File(.xlsx):
let 

    Source = Excel.Workbook(Web.Contents(OneDrive_File_Path), null, true),

    Src_Data = Source{[Item="data",Kind="Sheet"]}[Data],

    Promote_Headers = Table.PromoteHeaders(Src_Data, [PromoteAllScalars=true])

in

    Promote_Headers

Notes: here, Item="SheetName"

Comma Delimited CSV File(.csv) :

let

    Source = Csv.Document(Web.Contents(OneDrive_File_Path),[Delimiter=",", Encoding=65001]),

    Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

in

    Promote_Headers

3) Source: Local Files - File.Contents Method:
Excel File(.xlsx):
let
Source = 
Excel.Workbook(File.Contents("T:\T_Tech_Lab\PowerBI_Lab\DataSets\ds_Sample.xlsx"), null, true),
tbl_Sales_Sheet = Source{[Item="tbl_Sales",Kind="Sheet"]}[Data],
Promote_Headers = Table.PromoteHeaders(tbl_Sales_Sheet, [PromoteAllScalars=true])
in
Promote_Headers

Comma Delimited CSV File(.csv):
let
Source = 
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\csv_Input.csv"),[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers

Tab Delimited Text File(.txt):
let
Source = 
Csv.Document(File.Contents(""T:\T_Tech_Lab\PowerBI_Lab\DataSets\txt_TabDelimited_Input.txt"),[Delimiter="#(tab)", Columns=12, Encoding=1252]),
Promote_Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
Promote_Headers

Notes:
We can set the Columns property to null as Columns=null instead of specifying the number of columns, to load all.

Thanks, Tamatam