Calculate SUM of a field depending upon a filter of another table (not related)

[Anonymous]

I have a column Col1 Table A which I would like to SUM on depending on another table B. 

I have been trying out various DAX expression but without much success:

Please can someone shed some light. (Sorry I cannot share the pbi or the data).

Measure =
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), max(TableA[SameSomeTextCol]) = Selection)

[Mariusz]

Hi @Anonymous 

I can see that you have your solution already however, you might still consider the below alternative.

CALCULATE(
    SUM( TableA[Red] ),
    TREATAS( VALUES( TableB[SomeTextCol] ), TableA[SameSomeTextCol] )
)

Hope this helps
Mariusz

[v-lili6-msft]

HI, @Anonymous 

For your problem, It is a syntax mistake.

Measure =
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), max(TableA[SameSomeTextCol]) = Selection)

you should add a FILTER conditional in your formula as below:

Measure = 
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), FILTER(TableA,max(TableA[SameSomeTextCol]) = Selection))

or adjust your formula as below:

Measure 2 = 
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), TableA[SameSomeTextCol] = Selection)

Best Regards,

Lin

[Anonymous]

---

@v-lili6-msft wrote:

HI, @Anonymous 

For your problem, It is a syntax mistake.

Measure =
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), max(TableA[SameSomeTextCol]) = Selection)

you should add a FILTER conditional in your formula as below:

Measure = 
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), FILTER(TableA,max(TableA[SameSomeTextCol]) = Selection))

or adjust your formula as below:

Measure 2 = 
VAR Selection = SELECTEDVALUE(TableB[SomeTextCol], "")
RETURN
CALCULATE(sum(TableA[Red]), TableA[SameSomeTextCol] = Selection)

Best Regards,

Lin

---

No matter what I try I get tehe below error:

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I have even tried the below but gives the same error:

Redirects From Filter = 
VAR Selection = SELECTEDVALUE(NonVoice_Union[Journey], "")
RETURN
CALCULATE(sum(NonVoice_Redirects[Redirects]),filter(NonVoice_Redirects, NonVoice_Redirects[Journey]) = 
SEARCH(
    Selection,
    MAX(NonVoice_Redirects[Journey]),,BLANK()
)
)

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Try this formula:

Redirects From Filter =
VAR Selection =
    SELECTEDVALUE ( NonVoice_Union[Journey], "" )
RETURN
    CALCULATE (
        SUM ( NonVoice_Redirects[Redirects] ),
        FILTER ( NonVoice_Redirects, NonVoice_Redirects[Journey] 
            = Selection
    ))

and  Why you need to use SEARCH in it.

Best Regards,

Lin

[Anonymous]

Created a simple example with Table1 is just a list of colors and Table2 is a list of color with sales.  You can use these measures:

Total Sales = SUM( Table2[Sales] )

Value Selected = SELECTEDVALUE(Table1[Color])

Filtering without a Relationship=
IF ( 
    HASONEFILTER(Table2[Color]),
        CALCULATE(
            [Total Sales],
            FILTER(
                Table2,
                VALUES( Table2[Color] ) = [Value Selected]
            )
        )
)

[Anonymous]

---

@Anonymous wrote:

Created a simple example with Table1 is just a list of colors and Table2 is a list of color with sales.  You can use these measures:

Total Sales = SUM( Table2[Sales] )

Value Selected = SELECTEDVALUE(Table1[Color])

Filtering without a Relationship=
IF ( 
    HASONEFILTER(Table2[Color]),
        CALCULATE(
            [Total Sales],
            FILTER(
                Table2,
                VALUES( Table2[Color] ) = [Value Selected]
            )
        )
)

---

There is no error when I try this but I am not being able to use it e.g.g in a matrix or a table:

[Anonymous]

What error are you getting and give us at least 3 rows mock data by changing values?