topic Dynamic Measure and using her in one calculation in DAX Commands and Tips

Dynamic Measure and using her in one calculation

brunozanoelo — Mon, 16 Dec 2024 19:59:11 GMT

I have the following Table, with a Dynamic Parameter that changes the measure used in graphs:

Dimensão de Valores = {

("Valor de Venda", NAMEOF([Valor Total de Venda]), 0),

("Quantidade (PE)", NAMEOF([Total Quantidade (PE)]), 1),

("Quantidade", NAMEOF([Total Quantidade]), 2),

("Peso Bruto", NAMEOF([Total Peso Bruto]), 3),

("Peso Líquido", NAMEOF([Total Peso Líquido]), 4)

}

All of the "TOTAL" are SUM() measures.

However "Dimensão de Valores" returns me and String value, in face of this I´m facing two problems:
1) When using it, it doesn´t order descending/ascending automatically, when we change the parameter, we need to click in the datatable to proper order;
2) I have an calculation that needs to be changed as it change the parameter but i´m not finding a proper solution for this:

IF(MIN(Vendas[Ano])=MAX(Vendas[Ano]),BLANK(),CALCULATE(SUM(Movimentos[Valor de Venda]),FILTER(Vendas,Vendas[Ano]=MIN(Vendas[Ano]))))

Re: Dynamic Measure and using her in one calculation

VahidDM — Tue, 17 Dec 2024 02:47:38 GMT

Hi brunozanoelo

Short Answer:
Use a SWITCH function to map the selected text value to the corresponding measure, and then reference that measure in your calculations. For sorting, consider using a numeric column in the parameter table or field parameters for dynamic sorting.

Detailed Steps:

Map the Selected Parameter to a Measure:
Instead of returning a string measure name, use a SWITCH in a measure to choose the corresponding numeric measure. For example:

SelectedMeasureValue = SWITCH( SELECTEDVALUE('Dimensão de Valores'[Dimensão de Valores]), "Valor de Venda", [Valor Total de Venda], "Quantidade (PE)", [Total Quantidade (PE)], "Quantidade", [Total Quantidade], "Peso Bruto", [Total Peso Bruto], "Peso Líquido", [Total Peso Líquido], BLANK() )

Now SelectedMeasureValue returns the actual numeric measure, not text.

Use the Mapped Measure in Calculations: Your existing calculation:

IF( MIN(Vendas[Ano]) = MAX(Vendas[Ano]), BLANK(), CALCULATE( SUM(Movimentos[Valor de Venda]), FILTER(Vendas, Vendas[Ano] = MIN(Vendas[Ano])) ) )

Replace SUM(Movimentos[Valor de Venda]) with your new SelectedMeasureValue:

DynamicCalculation = IF( MIN(Vendas[Ano]) = MAX(Vendas[Ano]), BLANK(), CALCULATE( [SelectedMeasureValue], FILTER(Vendas, Vendas[Ano] = MIN(Vendas[Ano])) ) )

Handle Sorting: For sorting issues:
- Ensure your visuals are set to sort by this measure or by the corresponding numeric column.
- If you need dynamic sorting based on the parameter, consider:
  - Adding a numeric column to the parameter table and sorting by that column.
  - Using field parameters (if available) for more flexible sorting scenarios.

By using a SWITCH measure, you dynamically select the appropriate measure based on the parameter selection, enabling both correct aggregation and easier control over sorting.

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Dynamic Measure and using her in one calculation

brunozanoelo — Tue, 17 Dec 2024 13:05:21 GMT

Hello VahidDM ! It´s a pleasure to meet you, thank you for your help.

I tried the following solution and appears to work, until the part of the calculation, PowerBI Desktop gives me this error on visuals:

Error fetching data for this visual:
MdxScript(Model) (195, 5) Calculation error in the measure "Dimensão de Valores"[SelectedMeasureValue]: A [Values] is part of the composite column key, but not all columns in the composite key are included in the expression or its dependent expression.

Any suggestion?

Re: Dynamic Measure and using her in one calculation

brunozanoelo — Tue, 17 Dec 2024 13:05:03 GMT

Re: Dynamic Measure and using her in one calculation

brunozanoelo — Wed, 18 Dec 2024 13:17:40 GMT

VahidDM Hello Vahid, I´m trying the solution proposed in this article's but I´ve not suceeded yet.
https://community.fabric.microsoft.com/t5/Desktop/Field-Parameters-Dynamic-Visual-Title/td-p/2529607
https://www.sqlbi.com/blog/marco/2022/06/11/using-selectedvalue-with-fields-parameters-in-power-bi/

Hard to find an workaround.

Re: Dynamic Measure and using her in one calculation

brunozanoelo — Wed, 18 Dec 2024 14:22:57 GMT

VahidDM Hello Vahid, I´ve found one solution, using the integer field of my table and not the string one, in this case, PowerBI seens to understand the key.

MedidaSelecionada = SWITCH( SELECTEDVALUE('Dimensão de Valores'[Parâmetro Pedido]), 0, [Valor Total de Venda], 1, [Total Quantidade (PE)], 2, [Total Quantidade], 3, [Total Peso Bruto], 4, [Total Peso Líquido], BLANK() )

However, I´m facing now a format problem, since I have Currency (R$) and quantity format, I will change my calcs as the following:

Valores - Primeiro Ano = IF( MIN(Vendas[Ano]) = MAX(Vendas[Ano]), BLANK(), CALCULATE( [MedidaSelecionada], FILTER(Vendas, Vendas[Ano] = MIN(Vendas[Ano])) ) )

Valores - Primeiro Ano (Formatado) = SWITCH( SELECTEDVALUE('Dimensão de Valores'[Parâmetro Pedido]), 0, FORMAT([Valores - Primeiro Ano], "R$ #,##00.00"), 1, FORMAT([Valores - Primeiro Ano], "Standard"), 2, FORMAT([Valores - Primeiro Ano], "Standard"), 3, FORMAT([Valores - Primeiro Ano], "Standard"), 4, FORMAT([Valores - Primeiro Ano], "Standard"), BLANK() )

It appears to be working fine, in many calculations, however the Format is still and issue that doesn´t get automatically from the field configurations, I need to do it manually in each one of my calcs!

Thank you for ALL your help Vahid!

Re: Dynamic Measure and using her in one calculation

Anonymous — Thu, 19 Dec 2024 09:17:15 GMT

Hi brunozanoelo

Thanks for the reply from VahidDM .

brunozanoelo, good to know that you have solved the previous problem, for the problem you are facing now, if I understand correctly, do you need to make the data automatically into the corresponding format based on the Field parameter's options? I can't reproduce your scenario with the information so far, could you please provide some sample data and the expected results based on the sample data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu