Modify table that a SUMX points to

[Syndicate_Admin]

Hello good afternoon I hope you can help me.

It is possible that in a function of the STYLE of SUMX you can dynamically modify the table in which you make the sum, something like VAR _a = "test" RETURN SUMX(_a,[field]) where the value of _a would be changing dynamically according to some conditions.

This is because I have a dashboard where there are 4 tables of facts that can not be mixed with each other and depending on the filters that are selected has to show data from one table or another depending on the combination of filters.

for example that it is something like

VAR _tabla_variable = if(condition, "test1", "test2")

RETURN

SUMX(_tabla_variable, [value])

I tried to do something similar to that code but it marked me error, I hope you can help me.

Best regards.

[liuqi_pbi · Accepted solution]

Hi @Yeztrom 

The table expression cannot be dynamic. Instead, you can create 4 measures for the switch with different table names in them. For example, 

Total1 = SUMX('table1',[field])

Total2 = SUMX('table2',[field])

Total3 = SUMX('table3',[field])

Total4 = SUMX('table4',[field])

Then create a switch measure based on different conditions:

Result =
SWITCH (
    TRUE (),
    condition1, [Total1],
    condition2, [Total2],
    condition3, [Total3],
    condition4, [Total4]
)

Hope this helps

----------------------------------------------------------------------

If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!

[lbendlin]

You cannot do that. But what you CAN do is switch your SUMX computation.

Result = SWITCH([selector],1,sumx(a,[field]),2,sumx(b,[field],3,sumx(c,[field]),...)

This is partially dynamic and may give you what you need.

Can do:

Measure = if(TRUE(),sumx('Table',[Value]),sumx('Table 2',[Value]))

Cannot do: 

Measure = sumx(if(TRUE(),'Table','Table 2'),[Value]))

The essence of the problem is that the Power BI version of DAX does not support the EVALUATE function. If this is important to you please consider raising it at https://ideas.powerbi.com