Forum Discussion
Excel DAX query with bridge table using CROSSFILTER
JFGrenier Sure, that's the best decision, you may write a complex DAX that might break tomorrow if not written considering every possible detail, but a data model that's perfect would always give you the required soution with minimal DAX!
Let's say that Egypt is in 3 CountryGroup, thing is that I would have to copy the data associated with Egypt 2 more times with a different Id. I'm already at 14 millions rows in the DataTable and for a 32 bits system, I think it's near maximum. But what if I add multiple Id column in my CountryGroup table. I'll check how many country can be in multiple CountryGroup. If there are 15 country, I'd just add 15 columns of Id's and change the relationship accordingly. Do you think the DAX function RELATED could work? Or do you know of any other function that can change relationships? My DAX queries are built at run time via an Excel userform and trapping these exceptions would be easy. You point me in a new direction! Changing the model maybe my only solution!
But before that, I'll give one more shot at this query:
Where (syntax) could I add a filter to the DataTable[Year]=2019 && DataTable[HS2]=10 in the query below?
In the Summarize function, in the Calculate or else???
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( DataTable, DataTable[Year] ),
"Val",
CALCULATE (
SUM ( DataTable[Value] ),
CROSSFILTER ( DataTable[IdCountry], Country[IdCountry], BOTH ),
CROSSFILTER ( Country[IdCountry], CountryGroup[IdCountry], BOTH ),
FILTER ( CountryGroup, CountryGroup[IdCountryGroup] = 1 )
)
)
Thank's again!
Community ChampionJFGrenier Try this: for countryname in case there are more than 1 value you can use CONCATENATEX ( CALCULATETABLE construct. The file is below my signature:
Table =
ADDCOLUMNS (
CALCULATETABLE ( SUMMARIZE ( Data, Data[Year] ), Data[Year] = 2019 ),
"Val",
CALCULATE (
[Total Value],
CROSSFILTER ( Data[IdCountry], Country[IdCountry], BOTH ),
CountryGroup[IdCountryGroup] = 1
),
"CountryName",
CALCULATE (
DISTINCT ( CountryGroup[CountryGroupName] ),
CROSSFILTER ( Data[IdCountry], Country[IdCountry], BOTH ),
CountryGroup[IdCountryGroup] = 1
)
)
Hello Antriksh,
I tried your latest DAX solution in my real 'Data model' (what I showed is a scale down and translated version).
Had to adapt it a bit for DAX studio in french and checked several times to match your code!
The filter on IdCountryGroup do not seem to have any effect... The country group name changes accordingly but the returned values are always the same. It's the sum of all values of the Data table for the filtered year. Guess I will have to change my data model.
Thank you for your time and knowledge!
- AntrikshSharma
JFGrenier Sure, that's the best decision, you may write a complex DAX that might break tomorrow if not written considering every possible detail, but a data model that's perfect would always give you the required soution with minimal DAX!