• Facebook
  • Instagram
  • Twitter
  • Check
  • LinkedIn
Kontakt: +48 600 926 031
  • 0Shopping Cart
Zalnet- wdrożenia, konsultacje online Azure , administrowanie MS SQL , C# , optymalizacja baz danych
  • Strona główna
  • O mnie
  • Konsultacje
  • Szkolenia dla firm
  • Blog
  • Regulamin
  • Kontakt
  • Menu Menu

Wpisy

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

13 września 2021/w Power BI /Autor Beata Zalewa
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 RANKX. RANKX 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.

 

Learning DAX from scratch?Read our DAX learning guide!
START HERE

Download

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

Applying-color-banding-by-document-number-in-Power-BI.zip (2.17 MB)
https://zalnet.pl/wp-content/uploads/2021/09/pencil.jpg 1920 2560 Beata Zalewa https://zalnet.pl/wp-content/uploads/2021/09/gen_logo.png Beata Zalewa2021-09-13 21:31:222022-04-27 16:29:36Jak dodać kolorowe paski wg numeru dokumentu do raportu w Power BI?

Wyciśnij DAX jak cytrynę – prezentacja z wystąpienia na Data Community Poland Online

12 czerwca 2020/w Konferencje, Power BI /Autor Beata Zalewa
Czytaj dalej
https://zalnet.pl/wp-content/uploads/2022/04/lemon.jpg 667 1000 Beata Zalewa https://zalnet.pl/wp-content/uploads/2021/09/gen_logo.png Beata Zalewa2020-06-12 00:18:572022-04-30 13:51:44Wyciśnij DAX jak cytrynę – prezentacja z wystąpienia na Data Community Poland Online

Szukaj

Kategorie

  • ASP.NET Core
  • Azure
  • Bazy danych
  • C#
  • Docker
  • GitHub
  • Koderek
  • Konferencje
  • Mentoring
  • Microsoft SQL Server
  • NUnit
  • Optymalizacja baz danych
  • Podstawy C#
  • Power BI
  • Prywatne
  • SharePoint
  • SharePoint Online
  • T-SQL
  • Ubuntu
  • Uncategorized
  • Unit Tests
  • Visual Studio 2017
  • Visual Studio 2019
  • WordPress
  • Wyzwanie: pierwsze aplikacje C#

Facebook

Tagi

asp.net core Azure bazy danych c# docker sharepoint sharepoint online sql server ubuntu visual studio wordpress

Archiwum

Tagi

asp.net core (7) Azure (3) bazy danych (2) c# (2) docker (5) sharepoint (4) sharepoint online (4) sql server (3) ubuntu (4) visual studio (3) wordpress (5)

Social media

Dane firmy

ZALNET
Wola Radzięcka 62
23-440 Frampol
NIP: 717-111-99-64
REGON: 060594620

Menu

  • O mnie
  • Konsultacje
  • Szkolenia dla firm
  • Regulamin
  • Kontakt
Scroll to top

Ta witryna używa plików cookies. Korzystając ze strony wyrażasz zgodę na używanie cookies.

OK, akceptujęNie wyrażam zgody

Cookie and Privacy Settings



How we use cookies

We may request cookies to be set on your device. We use cookies to let us know when you visit our websites, how you interact with us, to enrich your user experience, and to customize your relationship with our website.

Click on the different category headings to find out more. You can also change some of your preferences. Note that blocking some types of cookies may impact your experience on our websites and the services we are able to offer.

Essential Website Cookies

These cookies are strictly necessary to provide you with services available through our website and to use some of its features.

Because these cookies are strictly necessary to deliver the website, refuseing them will have impact how our site functions. You always can block or delete cookies by changing your browser settings and force blocking all cookies on this website. But this will always prompt you to accept/refuse cookies when revisiting our site.

We fully respect if you want to refuse cookies but to avoid asking you again and again kindly allow us to store a cookie for that. You are free to opt out any time or opt in for other cookies to get a better experience. If you refuse cookies we will remove all set cookies in our domain.

We provide you with a list of stored cookies on your computer in our domain so you can check what we stored. Due to security reasons we are not able to show or modify cookies from other domains. You can check these in your browser security settings.

Other external services

We also use different external services like Google Webfonts, Google Maps, and external Video providers. Since these providers may collect personal data like your IP address we allow you to block them here. Please be aware that this might heavily reduce the functionality and appearance of our site. Changes will take effect once you reload the page.

Google Webfont Settings:

Google Map Settings:

Google reCaptcha Settings:

Vimeo and Youtube video embeds:

Privacy Policy

You can read about our cookies and privacy settings in detail on our Privacy Policy Page.

Zalnet-regulamin