Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
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
@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() ) )
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
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 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:
What error are you getting and give us at least 3 rows mock data by changing values?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |