The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi @Pikachu-Power ,
Not very clear what you mean, you mean the filter inside it calcuated.
It can not only restrict two conditions, but can have many restrictions. Use && as an intermediate connecting character, for example:
Measure =
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10))
If you mean the switch inside the condition, whether written or write directly measure the original formula is feasible
When a certain condition is met, the corresponding result will be obtained, if the condition is not met, the final result will be directly obtained.
Measure_switch =
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[flag])="PositionName1",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10)), SELECTEDVALUE('Table'[flag])="PositionName2",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10)),
SELECTEDVALUE('Table'[flag])="PositionName3",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName3"&&'Table'[ID]>=5&&'Table'[ID]<=11)),
'Table'[Measure 1]+'Table'[Measure 2]+'Table'[Measure 3])
If it does not meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Pikachu-Power ,
Switch can use the Else condition at the end. When the above conditions are not met, the result will be obtained directly.
I created some data:
1. Create meausre.
Measure1 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=3))
Measure 2 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=4))
Measure 3 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=5))
Measure 4 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=6))
Measure 5 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=7))
Measure_switch =
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[flag])="PositionName1",'Table'[Measure 1],
SELECTEDVALUE('Table'[flag])="PositionName2",'Table'[Measure 2],
SELECTEDVALUE('Table'[flag])="PositionName3",'Table'[Measure 3],
SELECTEDVALUE('Table'[flag])="PositionName4",'Table'[Measure 4],
SELECTEDVALUE('Table'[flag])="PositionName5",'Table'[Measure 5],
'Table'[Measure 1]+'Table'[Measure 2]+'Table'[Measure 3]+'Table'[Measure 4]+'Table'[Measure 5])
2. Result:
When you select PositionName6 that does not meet any of the above conditions, all of its additions are displayed
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks but looks complicated for my case 🙂 My measures have three or more restrictions and FILTER accept only two.
And what if
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[flag])="PositionName1",'Table'[Measure 1],
SELECTEDVALUE('Table'[flag])="PositionName2",'Table'[Measure 1],
SELECTEDVALUE('Table'[flag])="PositionName3",'Table'[Measure 3],
...
@Pikachu-Power , Try like
Measure_Column =
sumx(values(Table[Position]), SWITCH(TRUE(),
SELECTEDVALUE(Table[Position]) = "PositionName1", [Measure1],
SELECTEDVALUE(Table[Position]) = "PositionName2", [Measure1],
SELECTEDVALUE(Table[Position]) = "PositionName3", [Mesaure2],
SELECTEDVALUE(Table[Position]) = "PositionName4", [Mesaure2],
SELECTEDVALUE(Table[Position]) = "PositionName5", [Mesaure2],
blank()
))
Hello, seems not to work yet... I get BLANK for the Total.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
123 | |
111 | |
78 | |
74 |