Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
1 year ago

DAX - Cumulation based on division and previous filter contexts

Hello,   I don't have any report to share but I will try to make it clear. I want to cumulate the ratio based on daynumbers like in the image below. It is about how productive machines are and it's...
Need Help
naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
1 year ago

Hello,

In addition, this measure gives me the correct result:

_RatioCumulation =
VAR selected_date =
LOOKUPVALUE (
'DIM_Date_Disconnected'[Date],
'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] )
)
VAR CurrentDayNumber =
MAX ( DIM_Date[DayOfWeekNumber] )
RETURN
SUMX (
FILTER (
ALL ( DIM_Date[DayOfWeekNumber] ),
DIM_Date[DayOfWeekNumber] <= CurrentDayNumber
),
DIVIDE (
CALCULATE (
AVERAGEX (
FILTER (
DIM_Date,
YEAR ( [Date] )
= YEAR ( selected_date ) - 1
&& NOT ( [DayOfWeekNumber] IN { 6, 7 } )
),
CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )
),
REMOVEFILTERS ( DIM_Date[#Yesterday] ),
REMOVEFILTERS ( DIM_Machine ),
REMOVEFILTERS ( DIM_Department )
),
IF (
SELECTEDVALUE ( DIM_Date[DayOfWeekNumber] ) < 6,
CALCULATE (
AVERAGEX (
FILTER (
ALL ( DIM_Date[Date], DIM_Date[DayOfWeekNumber] ),
YEAR ( [Date] )
= YEAR ( selected_date ) - 1
&& [DayOfWeekNumber] = 2
),
CALCULATE ( SUM ( FACT_Productivity[MHOURS] ) )
),
REMOVEFILTERS ( DIM_Date[#Yesterday] ),
REMOVEFILTERS ( DIM_Machine ),
REMOVEFILTERS ( DIM_Department )
)
)
)
)

But it seems such overkill to copy/paste the same measure I used before. Referencing them should be much easier if possible?


Kind regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi naelske_cronos 

     

    You're right, it does seem a bit tedious to paste the same measures over and over again. We can simplify the code by referencing previously defined measures. Here is a simplified example:

    _RatioCumulation = 
VAR selected_date = LOOKUPVALUE ( 'DIM_Date_Disconnected'[Date], 'DIM_Date_Disconnected'[DateID], SELECTEDVALUE ( DIM_Date[DateID] ) )
VAR CurrentDayNumber = MAX ( DIM_Date[DayOfWeekNumber] )
RETURN
SUMX (
    FILTER (
        ALL ( DIM_Date[DayOfWeekNumber] ),
        DIM_Date[DayOfWeekNumber] <= CurrentDayNumber
    ),
    [_Ratio]
)

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.