Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
brunozanoelo
Post Patron
Post Patron

Dynamic Measure and using her in one calculation

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




1 ACCEPTED SOLUTION
brunozanoelo
Post Patron
Post Patron

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

View solution in original post

6 REPLIES 6
brunozanoelo
Post Patron
Post Patron

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

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

brunozanoelo
Post Patron
Post Patron

brunozanoelo
Post Patron
Post Patron

brunozanoelo_0-1734440566126.png

 

VahidDM
Super User
Super User

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:

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

 

 

 

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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.