Using field parameters and calculation groups for conditional formatting
ฝัง
- เผยแพร่เมื่อ 6 ก.ย. 2024
- How to apply conditional formatting on measures picked from a slicer and implemented using two techniques: field parameters and calculation groups.
Article and download: sql.bi/815509?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
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.
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. :)
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.
A very timely video for something I am currently working on. Thanks as always.
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
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.
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!
Ciao, interesting video keep up the good work. Regarding the selectedvalue function, you could use SELECTEDVALUE(Parameter[Parameter 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.
I've already done that too and it works smoothly. Also, you can add another column on the Field Parameter table with the mimic the measure name and use it on the SELECTEDVALUE
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.
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
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?
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.
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?
You should use SUMMARIZE, check www.sqlbi.com/articles/fields-parameters-in-power-bi/
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]).
the helper tables do make CG easier to use. Sure it is fine in this tiny example. in real life it is is a pain
apparently you can't. they never go into harvesting, like you can with regular tables...
бланодарю за разборчивый английский язык 🎉
thanks for beautiful spelling
excellent...thank you Sir
thank you
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?
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.
Field parameters work at the report level and work only in Power BI, not in Excel.
Use the calculation groups for that approach, you have more control: www.sqlbi.com/calculation-groups/
Love it
What is the hot key to move down a line ?
Handsome!
I'm wondering what is the specific reason for using "ALLNOBLANKROWS" instead than "ALL" in creating table "Measure CG Name" ...
To avoid circular dependency in case of relationships. See www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ and www.sqlbi.com/articles/understanding-circular-dependencies/
Yes, @@SQLBI, I know: we use ALLNOBLANKROWS to avoid circular dependencies. No doubt about this.
I wonder: how there could be a circular dependency in this case?
There are no relationships, no calculated columns...
I wonder: what I don't unterstand about circular dependencies yet?
Thanks
it is not even used in the card on the example file, he uses the field parameter, which ads to the confusion when trying to piece together
too advanced for me :/ I hope get that ceil ASAP i need it
7:57 Actually you can use color names to do conditional formatting by field value. As long as it's one of the 100 colors or so that you can use in html by using just the color name. Terrible idea though. Don't do it.