Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Syndicate_Admin
Administrator
Administrator

Modify table that a SUMX points to

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.

Yeztrom_0-1659721014922.png

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.

1 ACCEPTED SOLUTION
liuqi_pbi
Resolver III
Resolver III

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!

View solution in original post

2 REPLIES 2
liuqi_pbi
Resolver III
Resolver III

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.