Need to color record groups of the paginated report with alternate colors (Power BI Report Builder)

[E12345]

HI! 
I have a question about how to write an expression inside the "background" area of the Power BI Report Builder (for paginated reprots similar to SSRS) of the chart to highlight alternate ticket detail with alternating colors. Blow is my pictorial representation with some dummy data. For example, ticket number 1 needs to be grey, then ticket number 2 white, then ticket numbner 3 grey again. There may be multiple records per each ticket, so I need to somehow count when the ticket number changes and switch the color so th euser has easier time telling one ticket's details from another (all data is organized by ticket number). I am assuming MOD function needs to be used for this calculation. The data is eventually going to be exported to Excel, so I need it to be a part of the same table (no visible groupings should be intoduced, I am assuming - else it will break the "excel like" nature of the dataset. TY!

Same Data as text in case you need to copu and paste it:

Ticket_ID	Ticket_Descr
1	first
1	first
1	first
2	second
2	second
3	third
4	fourth
4	fourth
4	fourth
5	fifth
6	sixth
6	sixth
6	sixth
6	sixth
6	sixth
7	seventh
7	seventh
8	eightth

[v-yilong-msft]

Hi @E12345 ,

I create a table as you mentioned.

Then I create a measure and here is the DAX code.

Color =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table'[Ticket_ID] ) = 1, "#A9A9A9",
    SELECTEDVALUE ( 'Table'[Ticket_ID] ) = 3, "#A9A9A9",
    SELECTEDVALUE ( 'Table'[Ticket_ID] ) = 5, "#A9A9A9",
    SELECTEDVALUE ( 'Table'[Ticket_ID] ) = 7, "#A9A9A9",
    "#FFFFFF"
)

Next you can go to the Conditional Formatting and select background color.

You can do the same thing twice.

Finally you can get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[E12345]

Hi!
Thank you very much for trying to solve this problem. However, your solution is not the right one for me. I do not need a measure in Power BI, but I need a formula (VB) to place into the Power BI Report Builder application - into the background color of the details section. When I asked to add my sample dataset to the app, I meant you need to add it to "Power BI Report Builder" app, not to "Power BI Desktop" app ;). 

Also, your measure hard codes each ticket, but this is really only a sample data, there are throusands of "tickets" so a dynamic formula would be needed to figure out each new grouping. 

For your info, this is the envoronment for Power BI Report Builder. I use Dax as Query language, but inside the app still uses Visual basic. So, this question is for anyone who can work with Power BI Report Builder. 

Pasting a screenshot of the report - as you can see, this app is not the Power BI Desktop, it is an additional app for paginated reports building. 

[v-yilong-msft · Accepted solution]

Hi @E12345 ,

I did a test in Report Builder.

Then you can try this code.

=IIF(Fields!Ticket_ID.Value Mod 2 <> 0, "Gray", "Transparent")

Finally you can get this result.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[E12345]

Wowed! This line worked like a charm, and it is such a simple line, but I could not figure out the syntax! Thank you so much!!!! I changed the color to LightGrey

=IIF(Fields!Ticket_ID.Value Mod 2 <> 0, "LightGray", "Transparent")

 😉
A million thanks!

[E12345]

You can easily add the table I provided to the report to verify whether your code works... For some reason I am unable to find a solution, even when I grouped by Ticket ID (I keep getting an error or the groups of rows with the same ID are not highlighted properly). I will keep trying and if it works, will post my solution... So far no luck. Thank you for your help though. 

By the way, I figured out how to highlight alternate rows, but I need to highlight groups of rows, so it is not helping... This is the expression that I placed in the background area of the row that provides alternate colors:

=IIF(RowNumber(Nothing) MOD 2 = 0, "White", "LightGray")

[E12345]

if this is of any help - I tried this code but it did nto work... I need soemthing similar...

=IIF(RunningValue(Fields!Ticket_ID.Value, CountDistinct, "myDataSet") <> Previous(RunningValue(Fields!Ticket_ID.Value, CountDistinct, "myDataSet")), "LightGrey", "White")

[rajendraongole1]

Hi @E12345 - can you try below conditional formatting code.

=IIF(
MOD(
RunningValue(Fields!Ticket_ID.Value, CountDistinct, Nothing), 2
) = 0,
"White",
"LightGray"
)

[E12345]

Your formula sounds like it should do the trick, but it does not work... Perhaps groupings are needed in addition to the formula? Could you please add the dataset to the Power BI Report Builder and try it yourself? (save it in Excel and add it as a datasource). I feel like you have the potential to solve it. TY! 😉

[E12345]

For some reason I got the error:

"The 'BackgroundColor' expression for the text box 'Ticket_ID' contains an error: [BC30201] Expression expected.
----------------------------
The definition of the report '' is invalid.
----------------------------
An error occurred during local report processing."