The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
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.
Hi thanks for your reply.
I tried it but when i select a Value i got an empty values as result.
Hi here are the data and the pbix
https://www.dropbox.com/scl/fo/vd9sbbu444cq9bxtprkte/ABIkKw_t_kwAK9dUwMqsIsc?rlkey=wo53sxpy4n7ylqlw6...
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.
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....
@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.
Thanks that works very well 🙂
User | Count |
---|---|
77 | |
77 | |
36 | |
30 | |
28 |
User | Count |
---|---|
107 | |
100 | |
55 | |
49 | |
45 |