Forum Discussion

showy's avatar
showy
Icon for Helper II rankHelper II
1 year ago
Solved

Dynamic Measure with Fieldparameters

Hello

 

I have some sample data here.

 

I have a D_Item with the article 12345, which has 4 values (Value 1 - Value 4)

 

In the fact table I have a few quantities for the article 12345.

 

The tables are linked via the ItemNo (D_Item 1 -> N F_Sales)

SUM of Qty = SUM(F_Sales[Quantity])

 

I would now like to write another measure:

The measure should calculate the SUM of Qty * the selected value from the field parameter)

 

I have tried this for this:

Test =

 

VAR _Selection = SELECTEDVALUE(FieldParameterPrice[FieldParameterPrice Fields])

 

RETURN

[SUM] * _Selection

 

As soon as I click on a value in the field parameter, I get the following error message:

 

Error retrieving data for this visual.

 

MdxScript(Model) (9, 9) Calculation error in the measure "D_Item"[Test]: The value D_Item[‘Value1’] of type "Text" cannot be converted to type "Number".

 

The fields Value1, Value2, Value 3 and Value 4 are datatype currency. Does anyone know why it does not work and has a solution?

 

 

Need Help
Show and Tell
Tips and Tricks
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    showy could you please use beow updated measure to get correct Grand Total.

    _Measure(Updated)2 = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])

Var Result = 
SUMX(D_Item, 
    Var Quantity = CALCULATE(SUM(F_Sales[Quantity]))
    Var SelectedPrice = SWITCH (SelectedColumn,
                        "Price1", D_Item[Price1],
                        "Price2", D_Item[Price2],
                        "Price3", D_Item[Price3],
                        "Price4", D_Item[Price4],
                        BLANK())
    Var Result = Quantity * SelectedPrice //Final Result1
    Var Result2 = IF(ISBLANK(SelectedPrice), Quantity, Quantity * SelectedPrice) //Final Result2
    RETURN
    Result2 //Based on business needs chose any one result
    )
    
RETURN
Result

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

10 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    1 year ago

    showy ,

    This error is due to this [SUM] * _Selection. Here, you are trying to multiply a measure [SUM]  by _Selection which is a field reference not a number.


  • ajaybabuinturi's avatar
    ajaybabuinturi
    Icon for Super User rankSuper User
    1 year ago

    Hi showy, 
    Wanted to let you know that Filed Parameters are return column reference (Value1, Value2, etc) not Numbers(3,00, 2,50, etc). To Fix this issue use SWITCH DAX logic as mentioned below.

    _Measure =
VAR SelectedField = SELECTEDVALUE('FieldParameterPrice'[FieldParameterPrice Fields])
VAR SelectedPrice =
    SWITCH (
        SelectedField,
        "Value1", SELECTEDVALUE(D_Item[Value1]),
        "Value2", SELECTEDVALUE(D_Item[Value2]),
        "Value3", SELECTEDVALUE(D_Item[Value3]),
        "Value4", SELECTEDVALUE(D_Item[Value4])
    )
RETURN
[SUM of Qty] * SelectedPrice


    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

    • showy's avatar
      showy
      Icon for Helper II rankHelper II
      1 year ago

      Hi thanks for your reply.

      I tried it but when i select a Value i got an empty values as result.

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    1 year ago

    Cthe message says D_Item['Value1] is text.

     

    This suggest that the data type is text.

     

    I want to help you but your description is too vague. Please write it again.


    You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming


    * Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
    * Please DO give a simple non-technical functional description of what you want
    * Keep it simple and break large projects into smaller questions and ask just one question per ticket.
    * Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
    * Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
    * Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
    * Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
    * Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
    * Remember not to share private data ... we don't want you to get into trouble. ‌‌
    * Please click the thumbs up button for these helpful hints and tips. Thank you.


    Learn how to attach data in the forum using OneDrive:-
    * Save your file in a OneDrive folder
    * Right click on the file and click the “Share” blue cloud icon
    * Click the bottom “Copy” button
    * Click” Anyone with link can edit”
    * Click “Can Edit”
    * Click “Can View”
    * Click “Apply” button
    * Click “Copy”
    * Paste the generated link via the forum, email, chat, or any other method.
    * Helpers can then download your data, build a solution and share it back.


    Learn how to attach data in the forum using Dropbox:-
    1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
    2. Select File/Folder: Find the file or folder you want to share.
    3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
    4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
    5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.