Forum Discussion
calculate with filter
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!!
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
2 Replies
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
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!
