DAX Query using CROSSFILTER

[JFGrenier]

Hi!
I'm familiar with SQL but a DAX newbie!
Using DAX Studio from Excel / Power Pivot.
Part of my Data Model looks like this:

This sample query is functionnal and group some fields on table 1 with criteria on table 1 and 2 , and SUM the value

EVALUATE (
    SUMMARIZE ( 
	FILTER (Req_PaysM, [Annee] IN {2019,2020} && [Ech] = 1 && RELATED ( Part_Commercial[NoPays] ) = 9),
        [Annee],
        Mois[NomMois],
        "NomPays", FORMAT ( "Monde (tous les produits)", "@" ),
        "Echange", FORMAT ( "Importations", "@" ),
        "Valeur", SUM ( Req_PaysM[Valeur] )
    )
)

Now I wanna build a query that would somewhat do the same thing except that a criteria must be set in table 3
[NoRegroupement]=1 and group by [NomRegrp] also in table 3.

I read that I can use CROSSFILTER or USERELATIONSHIP to achive what I need but those function can only be used with the CALCULATE function.

So I'm having difficulties figuring out the correct syntax for grouping some fields from table 1 and 3 with criteria on table 1 and 3, and summing the value.

Any help and comments would be greatly appreciated.
Thank's
JF.

[amitchandak]

@JFGrenier , refer to this, how to use 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Greg_Deckler]

@JFGrenier  - This would be far easier with sample data and expected output. Second, not sure your relationship directions allow you to do the grouping from table 3 for items in table 1. 

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.

[JFGrenier]

Thank you Greg for showing me the right way to post my questions!

I'm a newbie here and feel that I'll be posting lots of questions.

I'll try to rephrase my question with more details as you suggest.

Not trying to be pushy here but I beleive this can be done.

Did a measure in PowerPivot that's working great with a Pivot Table and Slicers

RegPays:=CALCULATE(SUM(Req_PaysM[Valeur]);CROSSFILTER(Req_PaysM[Pays];Part_Commercial[NoPays];Both);CROSSFILTER(Part_Commercial[NoPays];Reg_Part_Commercial[NoPays];Both))

I'm trying to duplicate in a DAX query...

Using an Excel Userform that dynamically build a query and output raw data on a worksheet.

JF.

[Greg_Deckler]

Ah, you are using CROSSFILTER to modify the relationship direction. Was wondering where the CROSSFILTER piece came into play since I didn't see it in your original calculation. That's cool and all but then why not just make that relationship direction Both and not mess with it?

Again, this comes down to that this would be far easier if I could see what your source data looks like and then see what your expected result is

[JFGrenier]

@Greg_Deckler 

Here's a translated and scaled down version of the data model.

The Country table list 220 country. Each country has its unique code.

The CountryGroup table list economical region by country. Important to note that a single country can be in multiple CountryGroup. For example, Egypt can be in the CountryGroupName 'Africa' an also in CountryGroupName 'North Africa'

The DAX query should group Year, Month, CountryGroupName WHERE year=2019, CountryGroupCode=1 and sum the value.

The result would be:

2019  4   North America  2500
2019  8   North America  2500
2019 10  North America  10000

To my limited knowledge in DAX, I cannot make a relation from Data table CountryCode's field to CountryGroup table CountryCode's field because it will be a many to many relationship. That's why I'm using a bridge table (Country table). 

I hope this clears things up a bit.

Thank's again!

JF.