Forum Discussion
Helper IIISpecific value to be added from a value derived from a conditional value
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?
Helper IIIHi,
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”
))
RETURN
IF(
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_mayu
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.
- samioberoi
Hi ryan_mayu,
I created index column in both Table A and Table B to be able to make one to one relationship happen between the two. Beween the Table A and Table C (which i created to be able to dynamically filter based on DISTINCT_CODE column between the other tables as well) there is an inactive relationship for DISTINCT_CODE column from Table C and BAND_COL from Table A. Something like as i am trying to show in the image below. I may be doing something wrong.
It can be FLP1 will plus -573 (From Table B). The last DAX above called Measure is calculating FLP1 + (-573) properly, the only thing it is not doing is that it doesn't give me the aggregated values calculated for "ML (P1)", "PTML (P1)”.Me being not so knowledgeable about PBI i definitely am doing something wrong somewhere.
Much appreciated!
- ryan_mayu
the index are not matching for FLP1 and -573. One is index 4 and the other is 6. Still don't know the logic of calculation. If FLP1 will always add the subtotal in table B, you can try
=if (selectedvalue(DISTINCT_CODE) ="FL(P1)", XXXX)