Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
Got 2 tables, I want to switch or return value in another table then use that for a measure calculation.
See below measure created, it not working.
Selected_Measure =
VAR Selection = SELECTEDVALUE (Table[Region])
RETURN
SWITCH ( TRUE(),
Selection = "NE", Table2[Region]="NORTHEAST REGION",
Selection = "SW", Table2[Region]="SOUTHWEST REGION",
Selection = "WEST", Table2[Region]="WEST REGION",
Table2[Region]="ALL"
)
Solved! Go to Solution.
Hi @Tevon713
This error occurs because Table2[Region]=[Selected_Measure] portion is used as a filter expression in CALCULATE function. To avoid this error, you can try the following measure. Add a variable to get the measure value in advance, then use the value in CALCULATE.
Matches Count =
VAR __value = [Selected_Measure]
RETURN
CALCULATE (
DISTINCTCOUNT ( Table2[Acct Num] ),
FILTER (
Table2,
LOOKUPVALUE ( Table1[Acct Num], Table1[Acct Num], Table2[Acct Num] )
),
Table2[Region] = __value
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Hi @Tevon713
A measure is to display the results in a visual rather than in a data table. You can add below measure into a visual (e.g. a table visual) along with other necessary columns from Table2 to see the result.
Selected_Measure =
VAR Selection = SELECTEDVALUE ( Table[Region] )
RETURN
SWITCH (
TRUE (),
Selection = "NE", "NORTHEAST REGION",
Selection = "SW", "SOUTHWEST REGION",
Selection = "WEST", "WEST REGION",
"ALL"
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Thanks @v-jingzhang.
Tried previously. I'm getting this message when trying to use it return value. Intent is to look for if that account number exist in another table for that selected region.
A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Matches Count = CALCULATE(DISTINCTCOUNT(Table2[Acct Num]) , FILTER(Table2,LOOKUPVALUE(Table1[Acct Num],Table1[Acct Num], Table2[Acct Num])), Table2[Region]=[Selected_Measure])
Hi,
To overcome this error, you may also use FILTER function instead of direct filter expression. Filteration in measures does not work directly in expression, but works with FILTER function.
Example: Replace with below.
FILTER(Table2, [Region]=[Selected_Measure])
Hi @Tevon713
This error occurs because Table2[Region]=[Selected_Measure] portion is used as a filter expression in CALCULATE function. To avoid this error, you can try the following measure. Add a variable to get the measure value in advance, then use the value in CALCULATE.
Matches Count =
VAR __value = [Selected_Measure]
RETURN
CALCULATE (
DISTINCTCOUNT ( Table2[Acct Num] ),
FILTER (
Table2,
LOOKUPVALUE ( Table1[Acct Num], Table1[Acct Num], Table2[Acct Num] )
),
Table2[Region] = __value
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |