Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
I've enharited some dashboards and looking to simplify a measure now. This was originaly created in order to get certain values from a file if Period filter was 2021 P1-P3. Trying to simplify the measure and delete this exception now as it's not needed anymore. Everything else should be kept. Rather newbie here and struggling to follow the logic. Any ideas on how to approach this and delete the part i don't need anymore? TIA
Solved! Go to Solution.
This is based on my interpretation of your DAX code and your explanation or the requirement.
Answer as Number EUT YTD =
VAR latest_period =
MAX ( 'D_SalesPeriod'[Period] )
VAR year_val =
SELECTEDVALUE ( D_SalesPeriod[Cycle Year] )
VAR p1_p3 = "2021 P01-P03"
VAR not_total =
ISINSCOPE ( 'EUT tab_dimentions'[Q_lc1] )
VAR result =
IF (
not_total,
IF (
year_val = 2021,
CALCULATE ( SUM ( 'EUT P1-P3'[Value] ), 'D_SalesPeriod'[Period] = p1_p3 )
)
+ CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
),
CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
'EUT tab_dimentions'[Q_lc1] = "Conversion",
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
)
- CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
'EUT tab_dimentions'[Q_lc1] = "Losses",
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
)
+ IF (
year_val = 2021,
CALCULATE (
SUM ( 'EUT P1-P3'[Value] ),
'EUT tab_dimentions'[Q_lc1] = "Conversion",
'D_SalesPeriod'[Period] = p1_p3
)
- CALCULATE (
SUM ( 'EUT P1-P3'[Value] ),
'EUT tab_dimentions'[Q_lc1] = "Losses",
ALL ( 'D_SalesPeriod'[Period] ),
'D_SalesPeriod'[Period] = p1_p3
)
)
)
RETURN
RESULT + 0
Thanks! It has worked!
Hi @pandeion ,
Try to modify your formula like below:
Answer as Number EUT YTD =
VAR latest_period =
MAX ( 'D_SalesPeriod'[Period] )
VAR year_val =
SELECTEDVALUE ( D_SalesPeriod[Cycle Year] )
VAR not_total =
ISINSCOPE ( 'EUT tab_dimentions'[Q_lc1] )
VAR result =
IF (
not_total,
CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
FILTER (
ALL ( 'D_SalesPeriod' ),
'D_SalesPeriod'[Cycle Year] = year_val
&& 'D_SalesPeriod'[Period] <= latest_period
)
),
CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
FILTER (
ALL ( 'D_SalesPeriod' ),
'D_SalesPeriod'[Cycle Year] = year_val
&& 'D_SalesPeriod'[Period] <= latest_period
),
'EUT tab_dimentions'[Q_lc1] = "Conversion"
)
- CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
FILTER (
ALL ( 'D_SalesPeriod' ),
'D_SalesPeriod'[Cycle Year] = year_val
&& 'D_SalesPeriod'[Period] <= latest_period
),
'EUT tab_dimentions'[Q_lc1] = "Losses"
)
)
RETURN
IF ( ISBLANK ( result ), 0, RESULT )
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey tamerj1! Thanks for replying, code below.
This is based on my interpretation of your DAX code and your explanation or the requirement.
Answer as Number EUT YTD =
VAR latest_period =
MAX ( 'D_SalesPeriod'[Period] )
VAR year_val =
SELECTEDVALUE ( D_SalesPeriod[Cycle Year] )
VAR p1_p3 = "2021 P01-P03"
VAR not_total =
ISINSCOPE ( 'EUT tab_dimentions'[Q_lc1] )
VAR result =
IF (
not_total,
IF (
year_val = 2021,
CALCULATE ( SUM ( 'EUT P1-P3'[Value] ), 'D_SalesPeriod'[Period] = p1_p3 )
)
+ CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
),
CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
'EUT tab_dimentions'[Q_lc1] = "Conversion",
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
)
- CALCULATE (
SUM ( 'F_Form Answer'[Answer as Number] ),
'EUT tab_dimentions'[Q_lc1] = "Losses",
D_SalesPeriod[Cycle Year] = year_val,
'D_SalesPeriod'[Period] <= latest_period
)
+ IF (
year_val = 2021,
CALCULATE (
SUM ( 'EUT P1-P3'[Value] ),
'EUT tab_dimentions'[Q_lc1] = "Conversion",
'D_SalesPeriod'[Period] = p1_p3
)
- CALCULATE (
SUM ( 'EUT P1-P3'[Value] ),
'EUT tab_dimentions'[Q_lc1] = "Losses",
ALL ( 'D_SalesPeriod'[Period] ),
'D_SalesPeriod'[Period] = p1_p3
)
)
)
RETURN
RESULT + 0
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
73 | |
47 | |
45 | |
17 | |
17 |