Dax measures functioning of and on

[kashahzad]

Hi,

It's happening to me frequently now and I am getting frustrated. I created a dax measure to calculate the sales in specific regions and it worked initially and then suddenly it stopped and then the next day it started working again and then again it stopped working, is this a bug?  Below is the DAX formula and this is just one example.

sales by region = var reg = selectedvalue('Orders'[Region])

                                            return

                                            calculate(sum(Orders[Sales]), filter(Orders,'Orders'[Region]= reg))

[sturlaws]

Hi @kashahzad 

the measure you have written will only work if a single region is selected. I am guessing your experience in whether this work or not is related to this.

Cheers,
Sturla

[kashahzad]

@sturlaws 

Hi Sturlaws,

Thank you for looking into it and replying, do you find this formua incorrect? because it's not working and pulling nothing, but it was working previously and showing the sales by region and this is why I am confused.

sales by region = var reg = selectedvalue('Orders'[Region])

return   

calculate(sum(Orders[Sales]), filter(Orders,'Orders'[Region]= reg))

[sturlaws]

the code works:

as long as there is a single value for region in the current context. So if I choose two values in the slicer, it returns blank:

This is the expected behaviour, the SELECTEDVALUE-function returns blank if there is more than 1 distinct value in the current context. 

What kind of visual are you using this measure in? And what are you using to filter it?

[kashahzad]

@sturlaws 

I was trying to get the total sale value per region, so there are four regions and I want the formula to sum the sales within these four regions and show values in a table or the stacked bar chart.

North     ###

West      ###

South    ###

East       ###

[sturlaws]

Could you share the report you are working on? Or create a sample report where the issue is recreated and share it?

[sturlaws]

But then it is sufficient to write your measure as sales = sum(orders[sales]). The Power BI visual will handle the grouping into regions for you when it is on the axis of a visual

[kashahzad]

@sturlaws 

I totally agree, but since I am trying to learn Dax, so I am trying different scenarios to help me understand the behavior of different functions when combined together specially when variables are used to make the formulas efficient.