Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have these tables
Table2
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days
2 Negative overdue >8 days
3 Positive overdue >90 days
4 Negative overdue >90 days
Table1
Customer number Area >8 days >90 days Date MM_YYYY
1 Africa 10 -20 31.08.2020 Ago-20
2 Europe -3 15 31.08.2020 Ago-20
3 Europe 15 10 31.08.2020 Ago-20
4 Asia -4 30 31.08.2020 Ago-20
5 Africa -30 15 31.08.2020 Ago-20
6 Africa -5 -10 31.08.2020 Ago-20
In Table 2 column "Ago-20Africa" the output should filtering the Area, for example Area = "Africa"
.
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days 10
2 Negative overdue >8 days -35
3 Positive overdue >90 days 15
4 Negative overdue >90 days -30
I am using the measure:
Aug-20Africa = var test = CALCULATE(FIRSTNONBLANK ( 'Table1'[Customer number]; 1 );
FILTER(ALL('Table1'); 'Table1'[Area] = "Africa")) return IF([Index] = 1;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] > 0)/1000;IF([Index] = 2;CALCULATE(SUM('table1'[>8 days]);'table1'[>8 days] < 0)/1000;IF([Index] = 3; CALCULATE(SUM('Table1'[>90 days]);'Table1'[>90 days] > 0)/1000;if([Index] = 4;CALCULATE(SUM'Table1'[>90 days]);'Table1'[>90 days] < 0)/1000))))))
the result is that I get the same value no matter which "Area" I use as filter.
Thank you in advance!
Solved! Go to Solution.
Hi @Nun
I build Table1 and Table2 like yours to have a test.
Table1, MM_YYYYcolumn is a calculated column.
MM_YYYY = FORMAT(Table1[Date],"MMM")&"_"&FORMAT(Table1[Date],"YY")
Table2:
I build two measures to achieve your goal.
Aug_20Africa =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Aug_20Europe =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Result is as below.
You can download the pbix file from this link: Filter a text to get the open balance.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Nun So what should the output be for the given data? Can you post an image of your data model? You might be missing a relationship or have a relationship direction that needs changed.
the otuput, filtering the Area in Table1 (Africa in Table2 "Ago-20Africa), Europe in Table2"Ago-20Europe")
Index Overdue situation Ago-20Africa Ago-20Europe
1 Positive overdue >8 days 10 15
2 Negative overdue >8 days -35 -3
3 Positive overdue >90 days 15 25
4 Negative overdue >90 days -30 null
table1
table2
Using the previous formula, I am getting the same results in table2, if I filter Area "EMEIA" or "Americas" or "APAC" in column Ago-20 table2.
Thanks.
Hi @Nun
I build Table1 and Table2 like yours to have a test.
Table1, MM_YYYYcolumn is a calculated column.
MM_YYYY = FORMAT(Table1[Date],"MMM")&"_"&FORMAT(Table1[Date],"YY")
Table2:
I build two measures to achieve your goal.
Aug_20Africa =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Africa"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Aug_20Europe =
var _P8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N8 = CALCULATE(SUM(Table1[>8 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>8 days]<0&&Table1[MM_YYYY]="Aug_20"))
var _P90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]>0&&Table1[MM_YYYY]="Aug_20"))
var _N90 = CALCULATE(SUM(Table1[>90 days]),FILTER(Table1,Table1[Area]="Europe"&&Table1[>90 days]<0&&Table1[MM_YYYY]="Aug_20"))
return
SWITCH(MAX(Table2[Index]),1,_P8,2,_N8,3,_P90,_N90)
Result is as below.
You can download the pbix file from this link: Filter a text to get the open balance.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks a lot for your solution, istead to use SWICTH I used IF, because for some reason I got the same (negative) total for all the var.
So the I used:
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
77 | |
60 | |
58 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |