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.
I have a lot of terrible and complex measure from other projects.
Is there are any way to simplify and compress such measures like:
VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR subt =
CALCULATE (
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& NOT 'Table'[Type2] = 2
&& NOT 'Table'[Code] IN { valText1, valText12 }
)
),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
- CALCULATE (
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& 'Table'[Type2] = 2
&& NOT 'Table'[Code] IN { valText1, valText12 }
)
),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
- CALCULATE (
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& 'Table'[Code] IN { valText1, valText12 }
&& 'Table'[Sign] > 0
)
),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
- CALCULATE (
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& 'Table'[Code] IN { valText1, valText12 }
&& 'Table'[Sign] < 0
)
),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
+ - CALCULATE (
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER ( 'Table', 'Table'[Type1] IN { 2 } )
),
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
RETURN
SWITCH(TRUE(),
"505s" in distinct ( 'Table_2'[Column1] ) ,
- subt,
"948r" in distinct ( 'Table_2'[Column1] ),
subt
)
Solved! Go to Solution.
Try this:
col =
VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR ListOfDate =
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
VAR F =
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& NOT 'Table'[Code] IN { valText1, valText12 }
)
VAR subt1 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Type2] <> 2 ),
ListOfDate
)
VAR Subt2 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Type2] = 2 ),
ListOfDate
)
VAR Subt3 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Sign] > 0 ),
ListOfDate
)
VAR Subt4 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Sign] < 0 ),
ListOfDate
)
VAR Subt5 =
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER ( 'Table', 'Table'[Type1] IN { 2 } ),
ListOfDate
)
VAR Result = subt1 - Subt2 - Subt3 - Subt4 - Subt5
RETURN
SWITCH (
TRUE (),
"505s" IN DISTINCT ( 'Table_2'[Column1] ), - Result,
"948r" IN DISTINCT ( 'Table_2'[Column1] ), Result
)
Try this:
col =
VAR valText1 = "Text1"
VAR valText12 = "Text2"
VAR ListOfDate =
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
VAR F =
FILTER (
'Table',
'Table'[Type1] IN { 0 }
&& NOT 'Table'[Code] IN { valText1, valText12 }
)
VAR subt1 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Type2] <> 2 ),
ListOfDate
)
VAR Subt2 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Type2] = 2 ),
ListOfDate
)
VAR Subt3 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Sign] > 0 ),
ListOfDate
)
VAR Subt4 =
CALCULATE (
SUM ( 'Table'[Sum] ),
F,
KEEPFILTERS ( 'Table'[Sign] < 0 ),
ListOfDate
)
VAR Subt5 =
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER ( 'Table', 'Table'[Type1] IN { 2 } ),
ListOfDate
)
VAR Result = subt1 - Subt2 - Subt3 - Subt4 - Subt5
RETURN
SWITCH (
TRUE (),
"505s" IN DISTINCT ( 'Table_2'[Column1] ), - Result,
"948r" IN DISTINCT ( 'Table_2'[Column1] ), Result
)
@Analitika ,Can you share sample data and sample output in table format and formula logic.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |