Forum Discussion
Excel DAX query with bridge table using CROSSFILTER
Hi, this is my second take on a problem I have using the correct syntax on an Excel DAX query:
Relationships
Tables
I did a working measure in powerpivot using a pivot table with filters:
CountryGrp:=CALCULATE(SUM(DataTable[Value]);CROSSFILTER(DataTable[IdCountry];Country[IdCountry];Both);CROSSFILTER(Country[IdCountry];CountryGroup[IdCountry];Both))Can I duplicate this behavior with a DAX query?
I need to SUM the values where IdCountryGroup = 1 and Years = 2019
The result would be:
2019 North America 15000
This is where I'm at with no success:
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 )
)
)And how can I put a filter in 2 different tables?
Thank you for your help!
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!
11 Replies
JFGrenier Just change the data model a little bit and the DAX that you have to write will be short and simple, working PBI file is attached below my signature:
Hello AntrikshSharma!
And thank you for your reply!
There's a specific reason for the table CountryGroup. Sorry for not mentionning it before. It's because a country can be in multiple country group. For example: Egypt can be in the country group Africa and also in the country group North Africa. What you propose would create a many to many relationship.
Thank's again!
JFGrenier Still you should focus on changing the model, create 2 rows for Egypt in that case, with a unique key for both?
JFGrenier , Dax is the same at both places, should work.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
JFGrenier - Not quite sure I understand the full situation here. If you have the same data model in both places, Excel and Power BI the DAX should be the same.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Weird that my previous reply disappeared without any trace ...🤔
JFGrenier IMHO, in order to propagate filter from multiple(*) side to one(1) side of a relationship, expanded table would be a preferable choice.
As to your issue, filters on CountryGroup(*) can take effect to Country(1) this way,
CALCULATETABLE ( VALUES ( Country[IdCountry] ), CountryGroup )then such a filtering propagates naturally from Country(1) to DataTable(*) subsequently.
Pls try measure
Total Values := CALCULATE ( SUM ( DataTable[Value] ), CALCULATETABLE ( VALUES ( Country[IdCountry] ), CountryGroup ) )
