Forum Discussion
Specific value to be added from a value derived from a conditional value
Hi ryan_mayu,
Thanks for your reply and apologies for the mistake from my side in the Results Table. It should be like if it is specifically FL (P1) then it should take 109 from Table A and add/subtract that with -573 from Table B and should give me its value equal to something like 109 + (-573) = -464 and filtering should me done in the visual by the DISTINCT_CODE column from Table C. Rest of the codes like ML (P1) and PTML (P1) should be calculated normally.
Actual Amended Results Table
DISTINCT CODE FROM TABLE C | RESULTS COL |
ML (P1) | 1039 |
FL (P1) | -464 |
PTML (P1) | 836 |
Hope i could make it proper and clear this time and sorry for not explaining it properly in the first post.
Thanks.
- ryan_mayu1 year ago
Super User
since I can't find and relationship between FL(P1) row in Table A and the -573 in the table B, how did you get the band col? could you pls share the DAX?
- samioberoi1 year ago
Helper III
Hi,
This is the DAX i used for Band Col below:
BAND_COL = if (Table A[PROD] in {"TL","SS", "SE"}
&& Table A[CODE] = "BBC"
&& Table A[COUNTRY] in {"ENG", "UNK"},
"ML (P1),if (Table A[PROD] = "FL"
&& Table A[CODE] = "BBC"
&& Table A[COUNTRY] = "ENG",
"FL (P1),if (Table A[PROD] = "PT"
&& Table A[CODE] = "BBC"
&& Table A[COUNTRY] = "ENG"
"PTML (P1)"And this measure below is what i tried to create for the required calculation i am trying to get the right results for. It seems to be giving the right value for FL (P1) where the sum needs to be done from a figure from Table A with a value from Table B, but i can't get the other values as normal for "ML (P1)", "PTML (P1)”.
Measure =
VAR LT =
SWITCH(
TRUE(
SELECTEDVALUE(Table A[PROD] in {"TL","SS", "SE"}
&& SELECTEDVALUE(Table A[CODE] = "BBC"
&& SELECTEDVALUE(Table A[COUNTRY] in {"ENG", "UNK"},
"ML (P1)",SELECTEDVALUE(Table A[PROD] = "FL"
&& SELECTEDVALUE(Table A[CODE] = "BBC"
&& SELECTEDVALUE(Table A[COUNTRY] = "ENG",
"FL (P1)”,SELECTEDVALUE(Table A[PROD] = "PT"
&& SELECTEDVALUE(Table A[CODE] = "BBC"
&& SELECTEDVALUE(Table A[COUNTRY] = "ENG"
"PTML (P1)”VAR X90 = CALCULATE(
SUM(TABLE A [BALANCE]),TREATAS(VALUES(TABLE C [DISTINCT COUNT], TABLE A [BUCKET COL])
)VAR TBVALUE = CALCULATE(
LOOKUPVALUE(
TABLE B [SUB_AMOUNT],
TABLE B [DOM] = “MPA”
TABLE B [PTCOL] = “ICRP2”
))
RETURNIF(
LT = "FL (P1)”,
X90 + IF (ISBLANK (TBVALUE, 0, TBVALUE),
LT
Hope i could explain better and for any info please let me know.
Appreciate a lot for your effort to help.
- ryan_mayu1 year ago
Super User
still can't find any relationship between TABLE A and B.could you pls explain why FLP1 will minus -573? I can't find any related columns in both tables.