topic Re: metrics that filter by Country and you get the values by OU in DAX Commands and Tips

metrics that filter by Country and you get the values by OU

Anonymous — Fri, 28 Jan 2022 10:33:11 GMT

Hello

i have requirement where when you select country then you should get the values of respective OU.

example: if in Europe OU you have 2 countries(Spain, France), if you select one of the countries then you should get the sum of 2countries i.e., sum of Europe OU.

please help me with dax to create a measure using above requirement.

thanks in advance..

Re: metrics that filter by Country and you get the values by OU

amitchandak — Fri, 28 Jan 2022 10:39:53 GMT

Anonymous , try a measure like

measure =
Var _tab = summmarize(filter(Table, Table[Country] in allselected(Table[Country])), Table[OU])
return
calculate(Sum(Table[Value]), filter(all(Table[OU]), Table[OU] in _tab))

That will still keep selected country/countries

if you want display all countries , the have independent country, OU Table
measure =
Var _tab = summmarize(filter(Country, Country[Country] in allselected(Country[Country])), Country[OU])
return
calculate(Sum(Table[Value]), filter(Table, Table[OU] in _tab))

Re: metrics that filter by Country and you get the values by OU

ValtteriN — Fri, 28 Jan 2022 10:46:28 GMT

Hi,

You can use logic like this:

So here we will calculate sum of values based on material. i.e. if we have item1 or item4 selected the value will be 5

Dax:

Var example =

var _selection = MAX('Matrix example'[Material]) return

CALCULATE(SUM('Matrix example'[Value]),ALL('Matrix example'),'Matrix example'[Material]=_selection)

End result:

You can apply same logi, but instead of items and material you have continent and country.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: metrics that filter by Country and you get the values by OU

Anonymous — Thu, 03 Feb 2022 11:03:47 GMT

Hi Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a country dimension table(Don't create any relationship with the fact table)

Countries = VALUES('Table'[Country])

2. Create a measure as below to get the sum of value

Measure = VAR _selcountries = ALLSELECTED ( 'Countries'[Country] ) VAR _ou = CALCULATETABLE ( VALUES ( 'Table'[OU] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Country] IN _selcountries ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[OU] IN _ou ) )

Best Regards

Re: metrics that filter by Country and you get the values by OU

Anonymous — Fri, 04 Feb 2022 07:27:47 GMT

thanks for your quick response, i was able to reslove it.