Jak dodać kolorowe paski wg numeru dokumentu do raportu w Power BI?

14 maja 2020 roku miałam przyjemność poprowadzić zdalną

This article describes how to use conditional formatting with a DAX expression to color the rows of a table in Power BI based on the order number instead of using the alternate rows color formatting option.

 

Power BI offers the capability to show tables with alternating colored rows so to make it easier to read the content of a report.
Wyrażeń DAX możemy używać do formatowania warunkowego do pokolorowania wierszy tabeli w usłudze Power BI na podstawie numeru zamówienia zamiast korzystania z opcji formatowania kolorów alternatywnych wierszy.
Power BI oferuje możliwość wyświetlania tabel z naprzemiennie kolorowymi wierszami, aby ułatwić czytanie treści raportu.

You might want to use a similar alternate coloring style for a different purpose: highlighting all the lines of one same order. If you look carefully at the figure above, you can discover that there are indeed five orders visible, each with one or more products. But there is no visual indication of the rows belonging to the same order. A better format using alternate background colors would be the following.

The background color of the rows depends on Sales[Order Number]. The background color switches between white and light gray every time the order number changes, so all the rows of the same order have the same background color and can be easily identified. You cannot obtain this visualization by only using a Power BI style, because the coloring of a row depends on the actual data in it. You can achieve this goal by using the conditional formatting feature in Power BI. You can set the background color of a cell according to the value of a measure. Therefore, you need a DAX formula that returns two values: one for the white rows and one for the gray rows. The value returned by the measure must alternate between those two values with each consecutive order number.
A possible (but incomplete) solution would be a measure that returns 0 for even order numbers and 1 for odd order numbers. However, this formula does not guarantee that the values are changing for every order. In case you filter two orders with an even number in a row, they would be colored the same. If you look at the figures, all order numbers are even! Nevertheless, the idea of using odd and even order numbers looks promising. We only need to transform the order number into an integer that increases by one for each order, regardless of the actual order number.
The RANKX function in DAX serves exactly this purpose. We can rank the rows in the report by their order number, and then color the table in the report depending on whether the rank is odd or even. Besides, due to its simplicity this formula is a perfect opportunity to expand on one of the lesser known arguments of RANKX: its third parameter.
Let us start with the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
RankOrder =
VAR OrderNumbers =                      -- By using ALLSELECTED ( Sales[Order Number] )
    CALCULATETABLE (                    -- we would get all the order numbers
        VALUES ( Sales[Order Number] ), -- if there were no explicit filter on the
        ALLSELECTED ( )                 -- order numbers. Therefore, we use
    )                                   -- VALUES / ALLSELECTED to reduce the number of orders
                                         -- evaluated in the ranking.
VAR CurrentOrder =
    SELECTEDVALUE ( Sales[Order Number] )
VAR Result =
    IF (
        HASONEVALUE ( Sales[Order Number] ),  -- Check that only one order is visible.
        RANKX (
            OrderNumbers,               -- Table to build the lookup values.
            Sales[Order Number],        -- Expression to use during the iteration.
            CurrentOrder,               -- Value to rank. We must use SELECTEDVALUE
            ASC                         -- to retrieve the current row in the report.
        )
    )
RETURN
    Result

COPYDAX CONVENTIONSCODE #1

FORMAT CODE WITH 

The OrderNumbers variable stores the order numbers that are visible in the table visualization. Be mindful that we did not use ALLSELECTED( Sales[Order Number] ) because it would return all the values of the Sales[Order Number] column. Indeed, ALLSELECTED as a table function returns the filtered values only if the column is being actively filtered. We retrieve the order numbers visible in the current visual by using the VALUES function evaluated with ALLSELECTED as a CALCULATE modifier.
The CurrentOrder variable is important too. Each row of the table visual is showing exactly one order number. Therefore, each cell has a filter context that filters exactly one order. In this scenario, SELECTEDVALUE returns the value of the order number currently being filtered. You cannot omit SELECTEDVALUE, because the Sales[Order Number] column reference alone requires a row context which is not present in our formula.
The Result variable evaluates the ranking if and only if there is only one order number visible, to avoid any calculation for the total and for any subtotal that groups two or more orders. If there is only one order in the filter context, then RANKX provides the ranking.
If you have used RANKX in the past, you were likely ranking a measure like Sales Amount. When you apply RANKX to a measure, you typically only use the first two arguments of RANKXRANKX evaluates the measure during the iteration over the table provided in the first argument; because of the context transition, RANKX evaluates the expression of the measure in a filter context obtained by the row being iterated. After the iteration, RANKX re-evaluates the same measure in the original filter context to obtain the value to rank.
In our case, we are ranking a column value and not the result of a measure. Therefore, we do not rely on a context transition. During the iteration, RANKX evaluates the Sales[Order Number] expression within a row context corresponding to the row being iterated. At the end of the iteration, RANKX must obtain the value to rank, but can no longer use the Sales[Order Number] expression because the row context is missing in the original evaluation context.
The third argument of RANKX is now useful: RANKX uses the third argument to get the value to rank. If the third argument is missing, RANKX evaluates the same expression passed in the second argument in the original evaluation context, which is why you have probably never used it. In our example, we provide CurrentOrder as the third argument of RANKX, getting the rank of the order number displayed in the report.
You can see the result of the RankOrder measure in the following picture.

RankOrder is a number increasing by one each time the row shows a different order number, regardless of the actual Order Number value. We can create the BandNumber measure to transform this number into 0 or 1:

1
BandNumber := MOD ( [RankOrder], 2 )

COPYDAX CONVENTIONSCODE #2

FORMAT CODE WITH 

The BandNumber measure is easy to use in the conditional formatting options of Power BI to change the background of all the columns you want to format. Unfortunately, you have to apply this format to every column shown in the visual because it is not possible to set conditional formatting for the entire row.

The report now shows alternate colors depending on the order number. You can hide the RankOrder and BandNumber measures; indeed, they are just internal measures whose sole purpose is to improve data visualization.
As you see, DAX is not only useful to compute super-complex expressions. You can also rely on DAX to format your report the way you like, by combining the power of DAX with the flexibility of conditional formatting.

 

Download

Press the link below for access to the files used in this article.

How to add Google authentication to ASP.NET Core 2.2 app

https://medium.freecodecamp.org/authentication-using-google-in-asp-net-core-2-0-5ec32c803e23

Introduction

Sometimes, we want our users to log in using their existing credentials from third-party applications, such as Facebook, Twitter, Google, and so on. In this article, we are going to look into authentication of an ASP.NET Core app using a Google account.

Prerequisites

  • Install .NET Core 2.0.0 or above SDK from here.
  • Install the latest version of Visual Studio 2017 from here.

Create MVC Web Application

Open Visual Studio and select File >> New >> Project. After selecting the project, a “New Project” dialog will open. Select .NET Core inside the Visual C# menu from the left panel. Then, select “ASP.NET Core Web Application” from the available project types. Put the name of the project as GoogleAuthand press OK. Refer to this image.

After clicking OK, a new dialog will open asking you to select the project template. You can observe two drop-down menus at the top left of the template window. Select “.NET Core” and “ASP.NET Core 2.0” from these dropdowns. Then, select the “Web application(Model-View-Controller)” template. Click on the Change Authentication button, and a “Change Authentication” dialog box will open. Select “Individual User Account” and click OK. Now, click OK again to create our web app.

Before running the application, we need to apply migrations to our app.

Navigate to Tools >> Nuget Package Manager >> Package Manager Console.

It will open the Package Manager Console. Put in the Update-Databasecommand and hit enter. This will update the database using Entity Framework Code First Migrations.

Press F5 to run the application. You will see a homepage, as shown below.

Note the URL from the browser’s address bar. In this case, the URL is http://localhost:51792/. We need this URL to configure our Google app, which we will be doing in the next section.

Create the Google app

We need to create a new Google app on the Google API console. Navigate to https://console.developers.google.com/projectselector/apis/library and log in using your Google account. If you do not have a Google account, you need to create one. You cannot proceed without a Google account. Once you have logged in, you will be redirected to the API Manager Library page, similar to the one shown below.

Click on the Create button to move to the “New Project” page where you need to create a new project. The “Project name” field will be populated automatically with a default name provided by Google. If you want, you can override that with your own custom name. For this tutorial, we will be using the default name. Accept the terms of service and then click on the Createbutton.

Your project will be created successfully and you will be redirected to the API Library page similar to the one shown below.

Search for the Google+ API in the search bar and select the Google+ API from the search results. Refer to the below image.

After selecting the Google+ API option, you will be redirected to a page as shown below, where you need to click on the Enable button.

After this, the Google+ API will be enabled and you will be redirected to the API home page. Click on Create credentials button on the right side of the page to configure the secrets for your API.

You will see an “Add credentials to your project” form.

This form has three sections.

Fill in the details of the sections as described below.

Section 1 — Find out what kind of credentials you need

  • Which API are you using? — Google+ API
  • Where will you be calling the API from? — Web server (for example, Node.js, Tomcat)
  • What data will you be accessing? — User data

And click on the What credentials do I need button. You will be redirected to section 2

Section 2 — Create an OAuth 2.0 client ID

  • Name — The default value provided by Google.
  • Authorized JavaScript origins — Leave it blank.
  • Authorized redirect URIs — Give the base URL of your application with /signin-google appended to it. For this tutorial, the URL will be http://localhost:51792/signin-google. After entering the URL, press TAB to add the value.

After this, click on the Create client ID button. You will be redirected to section 3.

Section 3 — Set up the OAuth 2.0 consent screen

  • Email address — Select your email address from the dropdown. This value is masked in the above image for privacy.
  • Product name shown to users — Enter any product name. Here we are using “AuthDemo” as the Product name.

Note: Do not use the word “Google” in your product name. You will be prompted with an error and you won’t be allowed to create the app. This means “GoogleAuthDemo” is an invalid name.

Click on continue.

Your credentials have been created successfully. Click Download to download a JSON file to your local machine with all your application secrets, and then click Done to complete the process.

Open the just downloaded client_id.json file and make a note of the ClientIdand ClientSecret fields. We will need these values to configure Google authentication in our web app.

Configure your Web App to use Google authentication

We need to store the ClientId and ClientSecret field values in our application. We will use the Secret Manager tool for this purpose. The Secret Manager tool is a project tool that can be used to store secrets such as password, API Key, etc. for a .NET Core project during the development process. With the Secret Manager tool, we can associate app secrets with a specific project and can share them across multiple projects.

Open your web application once again and Right-click the project in Solution Explorer. Select Manage User Secrets from the context menu.

secrets.json file will open. Put the following code in it:

{
  "Authentication:Google:ClientId": "Your Google ClientId here",
  "Authentication:Google:ClientSecret": "Your Google ClientSecret here"
}

Now open the Startup.cs file and put the following code into the ConfigureServices method:

services.AddAuthentication().AddGoogle(googleOptions =>
{
    googleOptions.ClientId = Configuration["Authentication:Google:ClientId"];
    googleOptions.ClientSecret = Configuration["Authentication:Google:ClientSecret"];
});

In this code section, we are reading ClientId and ClientSecret for authentication purposes. So finally, Startup.cs will look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using GoogleAuth.Data;
using GoogleAuth.Models;
using GoogleAuth.Services;
namespace GoogleAuth
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }
        public IConfiguration Configuration { get; }
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();
            services.AddAuthentication().AddGoogle(googleOptions =>
            {
                googleOptions.ClientId = Configuration["Authentication:Google:ClientId"];
                googleOptions.ClientSecret = Configuration["Authentication:Google:ClientSecret"];
            });
            // Add application services.
            services.AddTransient<IEmailSender, EmailSender>();
            services.AddMvc();
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseBrowserLink();
                app.UseDeveloperExceptionPage();
                app.UseDatabaseErrorPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }
            app.UseStaticFiles();
            app.UseAuthentication();
            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

And with this, our application is ready.

Execution Demo

Launch the application and click Login on the top right corner of the home page.

You will be redirected to http://localhost:51792/Account/Login page, where you can see the option to login using Google on the right side of the page.

Clicking on the Google button will take you to the Google login page. There, you will be asked to fill in your Google credentials and authorize the Google app to use your Google account.

After successful authentication from Google, you will be redirected to a registration page inside your application where you need to fill in an email id to tag with your account. The Gmail id that you have used to login will already be populated in the Email id field. If you want to use another mail id, you can change it here.

Click register, you will be redirected to the home page again. But this time you can also see that your registered email is on the top right corner.

Conclusion

We have successfully created and configured a Google+ app and used it to authenticate our ASP.NET Core application.

You can get the source code from GitHub.

Please note that the secrets.json file contains dummy values. You’ll need to replace the values with the keys of your Google app before executing it.

You can also find this article at C# Corner.

You can check my other articles on ASP .NET Core here

Authentication Using Google In ASP.NET Core 2.2

Authentication Using Google In ASP.NET Core 2.0

Introduction

Sometimes, we want our users to log in using their existing credentials from third-party applications, such as Facebook, Twitter, Google, and so on. In this article, we are going to look into authentication of an ASP.NET Core app using a Google account.

Prerequisites

  • Install .NET Core 2.0.0 or above SDK from here.
  • Install the latest version of Visual Studio 2017 from here.

Create MVC Web Application

Open Visual Studio and select File >> New >> Project. After selecting the project, a “New Project” dialog will open. Select .NET Core inside the Visual C# menu from the left panel. Then, select “ASP.NET Core Web Application” from the available project types. Put the name of the project as GoogleAuthand press OK. Refer to this image.

After clicking OK, a new dialog will open asking you to select the project template. You can observe two drop-down menus at the top left of the template window. Select “.NET Core” and “ASP.NET Core 2.0” from these dropdowns. Then, select the “Web application(Model-View-Controller)” template. Click on the Change Authentication button, and a “Change Authentication” dialog box will open. Select “Individual User Account” and click OK. Now, click OK again to create our web app.

Before running the application, we need to apply migrations to our app.

Navigate to Tools >> Nuget Package Manager >> Package Manager Console.

It will open the Package Manager Console. Put in the Update-Databasecommand and hit enter. This will update the database using Entity Framework Code First Migrations.

Press F5 to run the application. You will see a homepage, as shown below.

Note the URL from the browser’s address bar. In this case, the URL is http://localhost:51792/. We need this URL to configure our Google app, which we will be doing in the next section.

Create the Google app

We need to create a new Google app on the Google API console. Navigate to https://console.developers.google.com/projectselector/apis/library and log in using your Google account. If you do not have a Google account, you need to create one. You cannot proceed without a Google account. Once you have logged in, you will be redirected to the API Manager Library page, similar to the one shown below.

Click on the Create button to move to the “New Project” page where you need to create a new project. The “Project name” field will be populated automatically with a default name provided by Google. If you want, you can override that with your own custom name. For this tutorial, we will be using the default name. Accept the terms of service and then click on the Createbutton.

Your project will be created successfully and you will be redirected to the API Library page similar to the one shown below.

Search for the Google+ API in the search bar and select the Google+ API from the search results. Refer to the below image.

After selecting the Google+ API option, you will be redirected to a page as shown below, where you need to click on the Enable button.

After this, the Google+ API will be enabled and you will be redirected to the API home page. Click on Create credentials button on the right side of the page to configure the secrets for your API.

You will see an “Add credentials to your project” form.

This form has three sections.

Fill in the details of the sections as described below.

Section 1 — Find out what kind of credentials you need

  • Which API are you using? — Google+ API
  • Where will you be calling the API from? — Web server (for example, Node.js, Tomcat)
  • What data will you be accessing? — User data

And click on the What credentials do I need button. You will be redirected to section 2

Section 2 — Create an OAuth 2.0 client ID

  • Name — The default value provided by Google.
  • Authorized JavaScript origins — Leave it blank.
  • Authorized redirect URIs — Give the base URL of your application with /signin-google appended to it. For this tutorial, the URL will be http://localhost:51792/signin-google. After entering the URL, press TAB to add the value.

After this, click on the Create client ID button. You will be redirected to section 3.

Section 3 — Set up the OAuth 2.0 consent screen

  • Email address — Select your email address from the dropdown. This value is masked in the above image for privacy.
  • Product name shown to users — Enter any product name. Here we are using “AuthDemo” as the Product name.

Note: Do not use the word “Google” in your product name. You will be prompted with an error and you won’t be allowed to create the app. This means “GoogleAuthDemo” is an invalid name.

Click on continue.

Your credentials have been created successfully. Click Download to download a JSON file to your local machine with all your application secrets, and then click Done to complete the process.

Open the just downloaded client_id.json file and make a note of the ClientIdand ClientSecret fields. We will need these values to configure Google authentication in our web app.

Configure your Web App to use Google authentication

We need to store the ClientId and ClientSecret field values in our application. We will use the Secret Manager tool for this purpose. The Secret Manager tool is a project tool that can be used to store secrets such as password, API Key, etc. for a .NET Core project during the development process. With the Secret Manager tool, we can associate app secrets with a specific project and can share them across multiple projects.

Open your web application once again and Right-click the project in Solution Explorer. Select Manage User Secrets from the context menu.

secrets.json file will open. Put the following code in it:

{
  "Authentication:Google:ClientId": "Your Google ClientId here",
  "Authentication:Google:ClientSecret": "Your Google ClientSecret here"
}

Now open the Startup.cs file and put the following code into the ConfigureServices method:

services.AddAuthentication().AddGoogle(googleOptions =>
{
    googleOptions.ClientId = Configuration["Authentication:Google:ClientId"];
    googleOptions.ClientSecret = Configuration["Authentication:Google:ClientSecret"];
});

In this code section, we are reading ClientId and ClientSecret for authentication purposes. So finally, Startup.cs will look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using GoogleAuth.Data;
using GoogleAuth.Models;
using GoogleAuth.Services;
namespace GoogleAuth
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }
        public IConfiguration Configuration { get; }
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();
            services.AddAuthentication().AddGoogle(googleOptions =>
            {
                googleOptions.ClientId = Configuration["Authentication:Google:ClientId"];
                googleOptions.ClientSecret = Configuration["Authentication:Google:ClientSecret"];
            });
            // Add application services.
            services.AddTransient<IEmailSender, EmailSender>();
            services.AddMvc();
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseBrowserLink();
                app.UseDeveloperExceptionPage();
                app.UseDatabaseErrorPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }
            app.UseStaticFiles();
            app.UseAuthentication();
            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

And with this, our application is ready.

Execution Demo

Launch the application and click Login on the top right corner of the home page.

You will be redirected to http://localhost:51792/Account/Login page, where you can see the option to login using Google on the right side of the page.

Clicking on the Google button will take you to the Google login page. There, you will be asked to fill in your Google credentials and authorize the Google app to use your Google account.

After successful authentication from Google, you will be redirected to a registration page inside your application where you need to fill in an email id to tag with your account. The Gmail id that you have used to login will already be populated in the Email id field. If you want to use another mail id, you can change it here.

Click register, you will be redirected to the home page again. But this time you can also see that your registered email is on the top right corner.

Conclusion

We have successfully created and configured a Google+ app and used it to authenticate our ASP.NET Core application.

You can get the source code from GitHub.

Please note that the secrets.json file contains dummy values. You’ll need to replace the values with the keys of your Google app before executing it.

You can also find this article at C# Corner.

You can check my other articles on ASP .NET Core here