calculate with filter

[MP_123]

hi,

i have two tables related by ID

in the second table, i have ID column and string column

for example

ID    String

1     ab

2     abc

with calculated measure, i concatenate chars together with conditions. sometimes the value is ab, somteimes abc.

i created another calculated measure that take one measure from the first table and filter the second table

caclulate (sum(measure from table 1),filter(table2,string=ConcatenateMEASURE))

when i look at the DB i see that the query kind of do LIke instead of exact (for example if my concatenate measure is ab, the query result is ab and abc), i tried to use 'EXACT' function and it doesn't work also.

only when i write 'ab' instead of the measure's valuee, the query result is correct.

is someone knows what's the problem?

thanks a lot!!

[v-ljerr-msft · Accepted solution]

@MP_123

---

only when i write 'ab' instead of the measure's valuee, the query result is correct.

---

As ConcatenateMEASURE is within the FILTER function, it will be evaluated for each row of the table2. So the result of ConcatenateMEASURE will be "ab" and "abc", not "ab".

In this scenario, using VAR function (whose value do not change once evaluated in the current context, even if the variable is referenced in another expression) should solve this issue. The formula below is for your reference.

=
VAR ConcatenateValue = ConcatenateMEASURE
RETURN
    CALCULATE (
        SUM ( 'table1'[measure_from_table1] ),
        FILTER ( table2, string = ConcatenateValue )
    )

 Regards

[anthop]

Hello,

I am new to Power BI; I have only defined basic measures so far.

- I assume the code snippet (from = VAR) - correct?

- Where/How do we define ConcatenateMEASURE?

Thanks for your help!