Forum Discussion
DAX Query using CROSSFILTER
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.
5 Replies
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.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.
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
