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.

Wprowadzenie do platformy Azure – utworzenie aplikacji ASP.NET Web API wspomaganej bazą Azure SQL Server

Tworzenie projektu ASP.NET Core 5.0 Web API 

Utworzenie nowego projektu ASP.NET Core
1. Uruchamiamy wiersz poleceń jako administrator (command prompt wywoływany za pomocą polecenia cmd):

Command Prompt

2. Tworzymy nowy projekt ASP.NET Core Web API. W tym celu w wierszu poleceń wpisujemy  poniższe polecenie i klikamy przycisk Enter. Polecenie to utworzy nam nowy projekt o nazwie BlueYonder.Flights na dysku w folderze Azure:

 dotnet new webapi --name BlueYonder.Flights --output C:\Azure\BlueYonder.Flights

Command prompt

3. Jak już projekt zostanie utworzony, zmieniamy domyślny folder wiersza poleceń na nasz folder z projektem za pomocą następującego polecenia:

cd C:\Azure\BlueYonder.Flights

Zmiana ścieżki

Utworzenie modelu Entity Framework Core
1. Abyśmy mogli używać Entity Framework Core w naszym projekcie, potrzebujemy zainstalować odpowiedni pakiet przy użyciu poniższego polecenia:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Dodanie biblioteki SQLServer

2. Aby przywrócić wszystkie pakiety i biblioteki, wymagane w naszym projekcie, uruchamiamy poniższe polecenie z poziomu wiersza poleceń:

dotnet restore

dotnet restore
3. Aby otworzyć naszą aplikację w Visual Studio Code, w wierszu poleceń wpisujemy poniższe polecenie:

code .

4. Folder z naszym projektem BlueYonder.Flights został otwarty w Visual Studio Code:

    • Klikamy Yes przy wiadomości Required assets to build and debug are missing from 'BlueYonder.Flights’.Add them?. Pozwoli to na zbudowanie (ang. build) naszego projektu i sprawdzenie, czy nie ma błędów w kodzie.
    • Jeśli pojawi się informacja There are unresolved dependencies, klikamy opcję Restore:

Visual Studio Code

5. Prawym przyciskiem myszy klikamy wewnątrz panelu File Explorer po lewej stronie i wybieramy opcję New Folder, a następnie nazywamy go Models:

 

dotnet restore

6. Prawym przyciskiem myszy klikamy na folderze Models, wybieramy opcję New C# Class i nazywamy ją Flight.cs. Opcja New C# Class jest dostępna dzięki rozszerzeniu do VS Code „C# Extensions”, autor: jchannon:
Utworzenie klasy Flight

7. Na górze klasy dodajemy następującą przestrzeń (ang. namespace):

using System;

Dodanie przestrzeni

8. Aby nasz model spełniał swoje zadanie, dodajemy następujące właściwości do klasy Flight:

public int Id { get ;set; }
public string Origin { get; set; }
public string Destination { get; set; }
public string FlightNumber { get; set; }
public DateTime DepartureTime { get; set; }

Dodanie właściwości do klasy
Stworzenie klasy DbContext
1. Prawym klikamy folder Models, wybieramy opcję New C# Class, a potem nazywamy ją FlightsContext.cs:
Ddoanie klasy FlightsContext
2. Na górze pliku FlightsContext.cs dodajemy następującą przestrzeń:

using Microsoft.EntityFrameworkCore;

Dodanie przestrzeni nazw
3. Nasza klasa FlightsContext będzie dziedziczyła z klasy DbContext, więc po nazwie klasy dodajemy takie dziedziczenie:

public class FlightsContext : DbContext

Dodanie DbContext
4. Wewnątrz klasy dodajemy konstruktor i właściwość Flights, która będzie zapewniała nam dostęp do naszych danych, przy użyciu następującego kodu:

public FlightsContext(DbContextOptions<FlightsContext> options) : base(options)
{
}
public DbSet<Flight> Flights { get; set; }

Dodanie konstruktora

5. Przechodzimy do klasy Startup.cs i dodajemy następujące przestrzenie:

using BlueYonder.Flights.Models;
using Microsoft.EntityFrameworkCore;

Dodanie przestrzeni

6. W metodzie ConfigureServices dodajemy połączenie (ang. connection string) do bazy danych Microsoft SQL Server, którą zaraz utworzymy w chmurze Azure:

services.AddDbContext<FlightsContext>(opt =>
opt.UseSqlServer(Configuration.GetConnectionString("defaultConnection")));

Dodanie connection string
Stworzenie kontrolera dla Web API
1. Prawym przyciskiem myszy klikamy na folderze Controllers i wybieramy opcję New C# Class. Nazywamy naszą klasę FlightsController.cs:
Dodanie kontrolera
2. Dodajemy referencję do folderu Models:

using BlueYonder.Flights.Models;

Dodanie przestrzeni
3. Dodajemy pole prywatne _context :

private readonly FlightsContext _context;

Dodanie pola _context
4. Aby wstrzyknąć kontekst bazy danych do kontrolera (inaczej mówiąc pozwolić na to, aby metody z naszego kontrolera mogły używać naszej bazy danych i operować na danych), używamy do tego konstruktora jak poniżej:

public FlightsController(FlightsContext context)
{
   _context = context;
}

Dodanie konstruktora
Stworzenie akcji i użycie kontekstu Entity Framework Core
1. Aby pobrać listę wszystkich lotów, dodajemy metodę Get, w której użyjemy pola  _context, dzięki któremu połączymy się do naszej bazy danych i z tabeli dbo.Flights pobierzemy wszystkie loty i zwrócimy je w postaci listy:

// GET api/flights
[HttpGet]
public IEnumerable<Flight> Get()
{
    return _context.Flights.ToList();
}

Dodanie metody Get
2. Do kontrolera dodajemy 3 nowe przestrzenie nazw (ang. namespaces):

using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;

Dodanie przestrzeni
3. Aby dodać nowy przelot i zapisać go w bazie danych, dodajemy metodę Post, która w parametrze flight będzie przekazywała właściwości z modelu Flight, a następnie dodawała ten nowy lot do innych lotów. Następnie zapisujemy lot do bazy danych, za pomocą metody SaveChanges wracamy do akcji Get, która ma wyświetlić nam dodany właśnie przelot:

// POST api/flights
[HttpPost]
public IActionResult Post([FromBody]Flight flight)
{
    _context.Flights.Add(flight);
    _context.SaveChanges();
    return CreatedAtAction(nameof(Get), flight.Id);
}

Dodanie metody Post
4. Nad klasą kontrolera dodajemy atrybut [Route(„api/[controller]”)] oraz dodajemy dziedziczenie z klasy ControllerBase, aby wskazać, że to jest API controller:

public class FlightsController : ControllerBase

Dodanie atrybutu API

Wdrożenie aplikacji na platformę Azure

Stworzenie Azure Web App i bazy danych Azure SQL Server
1. Otwieramy przeglądarkę.
2 Przechodzimy do adresu https://portal.azure.com.
3. Jeśli strona poprosi o dane do logowania, wpisujemy adres email, klikamy przycisk Next (Następny), wpisujemy hasło, a potem klikamy przyciskSign In (Zaloguj się).
4. Jeśli pojawi się okno z pytaniem Stay signed in?, klikamy Yes:
Logowanie do Azure'a
Uwaga: Jeśli podczas procesu logowania zostanie wyświetlona strona z monitem o wybranie konta z listy wcześniej używanych kont, wybieramy konto, z którego wcześniej korzystaliśmy, a następnie kontynuujemy podawanie swoich poświadczeń.
Dodawanie poświadczeń
5. Aby wyświetlić wszystkie zasoby na Azurze, klikamy przycisk +Create a resource:
Tworzenie zasobu
6. Aby wybrać szablon dla app service, w oknie Create a resource, w polu wyszukiwania wpisujemy Web App + SQL i wybieramy z podpowiedzi szablon Web App + SQL:
Tworzenie app service
7. W oknie Web App + SQL, klikamy przycisk Create:
Tworzenie app service
8. W oknie WebApp + SQL wypełniamy następujące pola:

  • W polu App Name, wpisujemy nazwę blueyonderflightsbz:

Uwaga: Nazwa App Name będzie częścią URL i będzie obsługiwana przez domenę azurewebsites.net. Nazwa naszej aplikacji musi być unikalna w obrębie domeny. Możemy także skonfigurować na Azurze ustawienia dla naszej własnej domeny i wtedy nazwa taka musi być unikalna w obrębie naszej domeny.

  • Wybieramy subskrypcję.
  • Resource Group, klikamy link Create new, and potem wpisujemy nazwę grupy zasobów BlueYonderFlightsRG i klikamy OK:

Tworzenie app service

  • W polu App Service plan/Location klikamy znak > po prawej stronie:

Tworzenie app service

  • Klikamy przycisk + Create new:

Tworzenie app service

  • W oknie New App Service Plan w polu App Service Plan wpisujemy BlueYonderFlightsPlan, z listy rozwijanej Location wybieramy lokalizację, w której chcemy hostować naszą aplikację:

Tworzenie app service

  • Klikamy strzałkę po prawej stronie obok pozycji Pricing tier. Na stronie Spec Picker możemy wybrać parametry hostingu dla naszej aplikacji. Na zakładce Dev/Test wybieramy maszynę F1 (zapewni nam to darmowe użytkowanie aplikacji przez godzinę dziennie)  i klikamy Apply:

Wybór App Service Plan

  • W oknie New App Service Plan klikamy przycisk OK:

App Service Plan

  • Na stronie Web App + SQL klikamy znak > po prawej obok pola SQL Database:

Baza danych

  • W oknie SQL Database wpisujemy następujące informacje:
    • W polu Name wpisujemy BlueYonderFlightsDB.
    • Klikamy link Select Server poniżej Target server:

Serwer bazodanowy

  • W oknie New server wpisujemy następujące informacje:
    • W polu Server name wpisujemy blueyonderflightsserverdbbz.

Uwaga: Nazwa serwera powinna być unikalna w ramach domeny .database.windows.net.

    • W polu Server admin login wpisujemy Admin123.
    • W polach PasswordConfirm password wpisujemy silne hasło.
    • W polu Location wybieramy lokalizację, w którym chcemy hostować naszą bazę danych.
    • Klikamy przycisk Select.

Dodanie serwera bazodanowegp

  • Klikamy link Configure database pod polem Pricing tier:

Dodawanie abonamentu

  • Wybieramy opcję Serverless i klikamy przycisk Apply:

Uwaga: W opcji Serverless opłaty są naliczane co sekundę w oparciu o zużycie vCores. W opcji Provisioned opłaty są naliczane co godzinę w oparciu o wcześniej zadeklarowaną liczbę vCores i wielkość miejsca na przechowywanie danych. Możemy także wybrać inne rodzaje i wielkości baz danych, w tym bazę darmową po kliknięciu na link Looking for basic, standard, premium?:
Dodanie abonamentu
Dodawanie abonamentu

  • Klikamy przycisk Select:

Widok ustawień bazy danych

  • Klikamy przycisk Create:

Wszystkie ustawienia aplikacji i bazy danych
9. Po utworzeniu bazy danych, aby przejść do naszej bazy danych, po lewej stronie klikamy na menu, a potem wybieramy SQL Databases:
Przejście do baz danych
10. W oknie SQL databases klikamy na link prowadzący do naszej bazy danych:
Widok bazy danych
11. W oknie bazy danych klikamy na zakładce Overview klikamy przycisk Set server firewall na pasku na środku pod nazwą naszej bazy danych:
Przejście do ustawień firewalla
12. W oknie Firewall settings klikamy przycisk + Add client IP. W regułach zostaje nam dodany nasz adres IP, który pozwoli na połączenie do bazy danych. Klikamy przycisk Save, a potem przycisk Continue:
Ustawienie firewalla
13. Na samej górze klikamy link do naszej bazy danych, aby powrócić na zakładkę Overview. W panelu po lewej stronie klikamy Query editor (preview):
Ustawienia bazay danych
14. Klikamy Login, wpisujemy nasze hasło, a potem klikamy OK:
Logowanie do bazy danych
15. Aby stworzyć nową tabelę w naszej bazie, wewnątrz zakładki Query 1, wklejamy następujący skrypt i klikamy przycisk Run:

CREATE TABLE [dbo].[Flights](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Origin] [varchar](50) NOT NULL,
[Destination] [varchar](50) NOT NULL,
[FlightNumber] [varchar](50) NOT NULL,
[DepartureTime] [date] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
));
GO

Stworzenie nowej tabeli
16. Zamykamy zakładkę Query 1 i w bazie danych sprawdzamy, czy nasza tabela jest widoczna:
Sprawdzenie, czy tabela jest widoczna
Wdrożenie  Azure Web App
1. Wracamy na stronę główną Azure Portal klikając link Home w lewym górnym rogu.
Uwaga: Jeśli pojawi się nam takie okno dialogowe klikamy przycisk OK:Powrót na stronę główną
2. Aby wyświetlić nasz App Service, klikamy menu po lewej stronie i wybieramy App Services:
Przejście do usług
3. Żeby zobaczyć konfigurację naszego w zakładce App Services klikamy link do naszej aplikacji:
Przejście do bazy danych
4. Aby dodać poświadczenia, dzięki którym będziemy mogli wdrożyć naszą aplikację do app service, pod sekscją DEPLOYMENT klikamy Deployment Center, a potem przechodzimy na zakładkę FTPS Credentials:
Dodanie poświadczeń
5. W sekcji User scope dodajemy następujące dane:

    • FTP/deployment username wpisujemy FTPBlueYonderFlightsUserBZ.
    • W polach Password i Confirm password wpisujemy złożone hasło.
    • Klikamy Save:

Dodanie szczegółów poświadczeń
Uwaga: Powyższe poświadczenia udostępniają opcje wdrażania aplikacji z wiersza polecenia.
6. W panelu po lewej stronie w sekcji Monitoring klikamy App Service Logs i zapisujemy w notatniku nazwę FTP/deployment username:
Zanotowanie poświadczeńń
7. Przełączamy się do Visual Studio Code.
8. Klikamy prawym przyciskiem myszy w panelu File Explorer po lewej stronie, wybieramy folder Properties.
9. Klikamy prawym przyciskiem myszy na folderze Properties, wybieramy New Folder i nazywamy ten folder PublishProfiles.
10. Do folderu PublishProfiles dodajemy plik Azure.pubxml:
Tworzenie Azure.pubxml
11. Wklejamy następujący kod, modyfikując odpowiednio nazwy PublishSiteName (FTP z notatnika) i Username (deployment username z notatnika) :

<Project>
<PropertyGroup>
<PublishProtocol>Kudu</PublishProtocol>
<PublishSiteName>blueyonderflights{TwojeInicjały}</PublishSiteName>
<UserName>FTPBlueYonderFlightsUser{TwojeInicjały}</UserName>
<Password>Pa55w.rd12345</Password>
</PropertyGroup>
</Project>

Dodanie zawartości do pliku Azure.pubxml
12. W wierszu poleceń wklejamy następującą komendę:

dotnet publish /p:PublishProfile=Azure /p:Configuration=Release
Publikowanie aplikacji

Uwaga: W przypadku, kiedy Publish nie przechodzi, należy zrestartować app service blueyonderflightsbz na Azurze (zakładka Overview). Po uruchomieniu możemy zobaczyć pustą tablicę jako rezultat:
Pusta tablica
Test Web API
1. Przechodzimy do portalu Azure.
2. Po lewej stronie klikamy SQL Databases.
3. Klikamy nazwę naszej bazy danych, a po przejściu do bazy danych po lewej stronie klikamy Query editor(preview).
4. Klikamy Login, a następnie wpisujemy nasze hasło.
5. W oknie klikamy +New Query i wywołujemy następujące polecenie, które stworzy nam 6 lotów:

INSERT INTO dbo.Flights(Origin, Destination, FlightNumber, DepartureTime)
VALUES('Paris', 'London', 'ABC123', '2021-06-13'),
('London', 'Warsaw', 'WEQ675', '2021-06-14'),
('Warsaw', 'New York', 'JHE543', '2021-06-15'),
('New York', 'Washington', 'CXD654', '2021-06-16'),
('Washington', 'Miami', 'MNK098', '2021-06-17'),
('Miami', 'Paris', 'DCT543', '2021-06-18');
GO

Dodanie dodanych do bazy
6. Aby pobrać wszystkie przeloty z bazy danych, wklejamy następujący skrypt do Query 1, and potem klikamy Run:

Select * from dbo.Flights;
GO
Sprawdzenie poprawności zapisania danych

7. Jeśli odświeżymy naszą aplikację, możemy zobaczyć, że aplikacja także zwraca nasze przeloty:
Sprawdzenie działania aplikacji
W ten oto sposób stworzyliśmy aplikację, za pomocą której możemy odczytać dane z bazy AzureSQL Server.
Gotowa aplikacja do pobrania z mojego Githuba.