Forum Discussion
create a measure that combine column values and measures based on a type column
Hi avcr29,
Nope, this measure is based on specific row content, when you use it in tachometer, measure can't get correspond result for summary records.
Regards,
Xiaoxin Sheng
HI avcr29,
You can try to use following formula to get related result if it works for your scenario:
result =
VAR _currType =
SELECTEDVALUE ( Table3[Type] )
VAR _currMonth =
SELECTEDVALUE ( Table3[Date (Month)] )
VAR temp =
ADDCOLUMNS ( Table2, "X measure", [X measure] )
RETURN
IF (
_currType = "type 1",
LOOKUPVALUE (
Table1[X column],
Table1[Date (Month)], _currMonth,
Table1[Type], _currType
),
IF (
_currType = "type 2",
MINX (
FILTER ( temp, [Date (Month)] = _currMonth && [Type] = _currType ),
[X measure]
)
)
)
If above not help, please share more detail information about your formulas.
Regards,
Xiaoxin Sheng
Hi Anonymous,
Thank you for your reply, i've tried the formula and i think it's almost there when i put it in a table the result is like this:
| Date(Month) | Type | result |
| March | type 1 | 55% |
| March | type 2 | -100% |
| May | type 1 | 0% |
| May | type 2 | 0% |
i think the minx function is the reason why the result in march type 2 is -100% instead of -0%. That's why i change it a little bit :
result =
VAR _currType =
SELECTEDVALUE ( Table3[Type] )
VAR _currMonth =
SELECTEDVALUE ( Table3[Date (Month)] )
VAR temp =
ADDCOLUMNS ( Table2, "X measure", [X measure] )
RETURN
IF (
_currType = "type 1",
LOOKUPVALUE (
Table1[X column],
Table1[Date (Month)], _currMonth,
Table1[Type], _currType
),
IF (
_currType = "type 2",
CALCULATE(if(and([x measure]>-1,[x measure]<0),0,[x measure]),
FILTER ( temp, Date(month) = _currMonth && [Type] = _currType )
)
)
there i get the wright result, tho when i try to use this measure in a tachometer it shows blank, how can fix this?
Thanks!
Hi avcr29,
Nope, this measure is based on specific row content, when you use it in tachometer, measure can't get correspond result for summary records.
Regards,
Xiaoxin Sheng