Dynamic Measure with Fieldparameters

[showy]

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?

[speedramps]

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.

[ajaybabuinturi]

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]

Hi thanks for your reply.

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

[ajaybabuinturi]

Hi @showy, could you please share some sample data of .pbix file

[showy]

Hi here are the data  and the pbix 
https://www.dropbox.com/scl/fo/vd9sbbu444cq9bxtprkte/ABIkKw_t_kwAK9dUwMqsIsc?rlkey=wo53sxpy4n7ylqlw6...

[ajaybabuinturi]

Thanks @showy for providing sample data. 
Wanted to let you know that the Paramter Table doesn't return the expected results.  Hence, 
I created a other table called PriceFields to ensure that to get slected Price value. Also I tweaked earlier provided Measure to achive requirement.
I am attching a .pbix file for reference.

Table: 

PriceFields = 
DATATABLE(
    "PriceColumn", STRING,
    "Index", INTEGER,
    {
        {"Price1", 0},
        {"Price2", 1},
        {"Price3", 2},
        {"Price4", 3}
    }
)

_Measure(Updated):

_Measure(Updated) = 
Var SelectedColumn = SELECTEDVALUE('PriceFields'[PriceColumn])
Var SelectedPrice = SWITCH(SelectedColumn,
                    "Price1", SELECTEDVALUE('D_Item'[Price1]),
                    "Price2", SELECTEDVALUE('D_Item'[Price2]),
                    "Price3", SELECTEDVALUE('D_Item'[Price3]),
                    "Price4", SELECTEDVALUE('D_Item'[Price4]),
                     BLANK()
                    )
Var Result = [SUM] * SelectedPrice //Final Result1
Var Result2 = IF(Result = BLANK(), [SUM], Result) //Final Result2
RETURN
Result2 //Based on business needs chose any one result

Expected Result:

Here is the .pbix file
https://drive.google.com/file/d/1uz7dlN3ec8fjEdJiMV_VecBU7oHzf6w5/view?usp=sharing 

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]

Hi @ajaybabuinturi this works very good and it the behaviour i wanted.

I tried it now with multiple Items but now i am facing a strange topic. I added 2 more items and Entry in F_Sales.
The switch between Price1 - Price 4 works and the values are correct but the total sum dont reacts to the selection. 

Do u also have a solution for that?

 I also updated the dropbox link with the current data

I think i have to use SUMX but i dont get it right....

[ajaybabuinturi · Accepted solution]

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

[showy]

Thanks that works very well 🙂

[Arul]

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