Using field parameters and calculation groups for conditional formatting

How to apply conditional formatting on measures picked from a slicer and implemented using two techniques: field parameters and calculation groups.

[aleksandaratanasovl]

I want to say thank you for the great content you and the SQLBI team provide to the public. Wishing you good health in the future and may your curiosity never fade.

[tamerjuma]

Thank you for highlighting on this on this particular deficiency of tabular editor. I love tabular editor but you are absolutely right, sometimes it gets intricate.
I would like also to mention that the same can be accomplished with a disconnected table without the need of either field parameter or tabular editor.

[nishantkumar]

Awesome, I did come across this scenario where I wanted something similar. I used selectedvalue and it didn't work.
For changing the color you can use Field Value instead of Rules for fx. Since measure is returning the color names it will be automatically get detected.
Thank you so much. I have learned a lot from you. :)

[simonloughnane]

A very timely video for something I am currently working on. Thanks as always.

[joaoluismartins]

Great video!!

Is there any drawback using another calculation group with higher precedence in order to come up with the colors? This way, we avoid the need of a particular table for colors and also adjusting the calculation items on the original Calc Group.

Cheers!

[iliassbz]

Ciao, interesting video keep up the good work. Regarding the selectedvalue function, you could use Fields]) and switch based on that field.
i don't know if there is any limitation or side effects in doing that but it works.

[AgulloBernat]

It is possible to create a calculated column on the field parameter table that is equal to the first column. On that column you can use selectedvalue without any trouble.

[jonashagg]

Great video! I came across this when thinking about the problem of calculating percentage of parent value in any matrix-hierarchy chosen by a user by selecting multiple field values in a 'field parameter slice and dice setup'. Do you think there is a way to solve that problem using similar techniques?

[gregoryoliveira]

There is something that I have been trying for a long time: use a column in a field parameter to rank. My first shot was inspecting the field parameter column order and using it in a switch function. However, this became a nightmare. Then, my second shot, which is working these days, was to use calculation groups. Could you recommend another patch?

PS: I really can not analyze if this solution was a best practice. Reading your book, I understand that we should avoid complex calculations inside calculation groups, but this was the best performance solution I brought.

[scooterza]

Hello Alberto! I have a scenario where I am using a Field Parameter that references 2 measures, both of which are using the USERELATIONSHIP to calculate values using inactive relationships (Primary relationship is Orders.[OrderDate] to Date.[Date]. Inactive relationships are Orders.[PickingDate] to Date.[Date] and also Orders.[ShipDate] to Date.[Date].) The measures are "Orders by Picking Date" and "Orders by Shipping Date".

This works well and my resultant column chart shows the order quantity dynamically based on selected slicer ("Orders by Picking Date" and "Orders by Ship Date"). However, in the order details table below there is a problem. The active relationship is used, so it uses the join Orders[OrderDate] to Date.[Date]. In my column chart, it shows eg 3 orders shipped on 10 October. However, when I click on that column for 10 October, the details table shows just 1 order because it is using the Orders.[OrderDate] to Date.[Date] and picks up that there was 1 order on 10 October.

Any suggestions on how to handle this? Thank you

[RandomlyWisdom]

Few observations:
7:35 Instead of Rules in Format style, if we choose Field Value, Power BI automatically detects the color from the color strings which we have mentioned in the [Dynamic Color] measure. This is quite amazing. But this is limited to popular color names I guess.
8:28 When nothing is selected in the slicer, it defaults to black color as we have mentioned. But the measure in the KPI card will be the last selected one. It's not always the Sales Amount because it is formatted as black. This was little confusing at first glance.

[elrevesyelderecho]

0:00 - 1:35 Intro
1:36 - 9:18 Colour switch using field parameters - MAX instead of SELECTVALUE
9:19 - 19:15 Previous exercise using Calculation Groups
19:16 Ciao

[TomGee-nn]

Is there a way to conditionally format a calculation item that is shown in a table. For example if i have the calc items, one that calculates sales amount for a selected time range, another calc item that is same period last year, and a third that computes the percentage change between the two. I want to format that percentage change red if the result is negative in the table visual, but I can't figure out how to do so!

[Hent]

бланодарю за разборчивый английский язык 🎉

thanks for beautiful spelling

[giovanniguarino]

Hello and thank you very much.
The question is if you select with CTRL more than one measure in a parameter slicer the result of the SELECTEDVALUE will be null.
In this case how to intercept what value was selected if I have already one option selected?

[chrisinbcn]

Hi, first of all thanks for the content. In this particular video I don't understand why using a helper table when you can get the selected calculation item using MAX (or even SELECTEDVALUE) for the Measure Calculation Group with MAX('Measure CG'[Measure CG]) or SELECTEDVALUE('Measure CG'[Measure CG]).

[luigir]

Question: If we were to have a measure of Sales and the CG does a comparison of YoY, YoY Var, and YoY Var% and we show this on a matrix. Is there a way to conditional color the YoY Var %? to say Green if its over 0% and red if its below it?

[ivysangel]

Hello, is there a function that dynamically picks the measure being evaluated? I am trying to create a measure to be used for font formatting and I dont want to input measures again and again.

[marilenadonofrio]

Handsome!
I'm wondering what is the specific reason for using "ALLNOBLANKROWS" instead than "ALL" in creating table "Measure CG Name" ...

[teddyfabriciocordovasaenz]

too advanced for me :/ I hope get that ceil ASAP i need it