Using field parameters and calculation groups for conditional formatting

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.

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

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]).

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?

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?

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.

What is the hot key to move down a line ?

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

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

thanks for beautiful spelling

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 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

Hello, I would greatly appreciate it if someone could assist me with the following issue:

I have a commission structure based on sales. From January 2023 to August 2023, the commission rate was 0.005 times the sales:

Commissions = Table[Sales] * 0.005

However, starting from September 2023, the commission rate has changed to 0.007. To accommodate this change, I had to modify the measure as follows:

Commissions = SWITCH( TRUE(), SELECTEDVALUE(Calendar[Year])>=2023 && SELECTEDVALUE(Calendar[Month_N])>8, Table[Sales] * 0.007, Table[Sales] * 0.005 )

The problem I'm facing is that I have two slicers: one for the year and the other for months. When I enable the "single select" option and select 2023 for the year and September or later for the month, everything works correctly. However, if I uncheck the "single select" option for the slicers and select 2023 for the year along with both August and September (to view two months simultaneously), the commissions for both months are calculated at the rate of Table[Sales] * 0.007, as the year is 2023 and at least one of the selected months is less than 8.

I can calculate commissions in a column and use it as a result (and this would work fine) but I want to avoid creating an additional column as it would consume extra space. Can someone please help me craft a measure that works correctly even when I select two or more months simultaneously? Your assistance would be greatly appreciated.

What is my preferable result:
1) I don't want to use "use single select" on slicers
2) If you select year 2023 and Months August AND September it should show the rightly calculated output
(example: if sales in august = 1000, then commission is 1000*0.005 = 5, if sales in September 2000, then commission = 2000*0.007= 14. If NOW i select 2023 year and AUG AND SEP it SHOULD SHOW commissions 14+5 = 19, but it shows 21 :( (I understand why it does this but can I avoid using "single" select or creating a column for commissions?)

Thanks.

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

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!

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.

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.

thank you

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. :)

excellent...thank you Sir

Love it