need help with dax query

[Herndon_powerbi]

Hi,

       My dax query is below:

table1=
var _a=
row( "color","1",
"Atrribute","budget",
"value",calculate(sum(table2[column1]),table3[column1]="value1",table4[column2]="value2"))

var _b=
row( "color","2",
"Atrribute","financial",
"value",calculate(sum(table7[column1]),table3[column1]="value1",table4[column2]="value2"))

Return union(_a,_b)

          None of the external slicers or filters are not working. What should I add to make it work to all filters and slicers?

Thanks inadvance

[PaulOlding]

Hi @Herndon_powerbi 

That DAX is creating a table in your model.  Tables are generated at dataset refresh only, not at report runtime, and are not re-calculated when users interact with the report.  So filters and slicers will not apply.

Measures are calculated at report runtime so this is what you need.  However, measures must return a single value, not a table.  What did you want to do with that table once you'd created it?

[Herndon_powerbi]

I agree with your opinion. Since I am a new user of power bi, I am not sure about this. Now I am good. I need to create a table as I mentioned above. Anyhow thanks for your help.

[v-jingzhang]

Hi @Herndon_powerbi 

If you want to see filtered budget and financial values in a table visual in the report, there is a workaround you can try. First create a table with below DAX:

table1 = 
VAR _a =
    ROW (
        "color", "1",
        "Atrribute", "budget"        
    )
VAR _b =
    ROW (
        "color", "2",
        "Atrribute", "financial"        
    )
RETURN
    UNION ( _a, _b )

Then create two measures with original CALCULATE statement. 

value_a = calculate(sum(table2[column1]),table3[column1]="value1",table4[column2]="value2")

value_b = calculate(sum(table7[column1]),table3[column1]="value1",table4[column2]="value2")

Create a third measure with SWITCH function and above two measures. 

Measure_value = SWITCH(SELECTEDVALUE(table1[Atrribute]),"budget",[value_a],"financial",[value_b])

Put color, Attribute columns and the third measure into a table visual. Now you can use external slicers and filters to change the measure values. 

I attached a simple demo with what-if parameter slicers. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Greg_Deckler]

@Herndon_powerbi Perhaps try adding ALLSELECTED statement in your CALCULATE statements. Another thing to try, try removing your CALCULATE statements from the ROW statement and put them in variables.