Struggling with dax

DAX Report Design Showcase
1

Hello Team,

I am struggling with dax where i created this table and now i am having issues with circular dependeny error.can you please help me resolve this issue
Here is the error
A circular dependency was detected: % Customer Time[% Customer Time-c26dbacb-936a-409f-9d6d-a4a6f95d4de6], 18ba9166-d4cb-4fc9-98f8-c30978977a4a, % Customer Time[Date], % Customer Time[% Customer Time-c26dbacb-936a-409f-9d6d-a4a6f95d4de6].
VAR _temp =
– FILTER (
– CALCULATETABLE(
FILTER (
SUMMARIZECOLUMNS (
‘WW HC tracker’[Name],
‘WW HC tracker’[Approach],
‘Date-Snapshot’[YEAR_MONTH_NBR],
– ALL(‘WW HC tracker’),
–TREATAS ( { “Daniel Vergara” }, ‘WW HC tracker’[Name] ),

            FILTER (
                'Date-Snapshot',
                'Date-Snapshot'[Date] = FIRSTDATE( 'Date-Snapshot'[Date] )
                    && 'Date-Snapshot'[Date] < TODAY ()
            ),

            "@Total Hours", [Customer time (hours)],
            "@Working Hours", [FY working hours YTD],
            "@Start date",
                COALESCE (
                    SELECTEDVALUE ( 'WW HC tracker'[Start date] ),
                    CALCULATE ( MAX ( 'WW HC tracker'[Start date] )
                    , REMOVEFILTERS ( 'Date-Snapshot' ) )
                ),
            "@End date",
                COALESCE (
                    SELECTEDVALUE ( 'WW HC tracker'[End date] ),
                    CALCULATE ( MAX ( 'WW HC tracker'[End date] )
                    , REMOVEFILTERS ('Date-Snapshot' ) )
                )
       ),
       NOT ISBLANK ( [@Total Hours] )
    )
   )

RETURN
SELECTCOLUMNS (
_temp,
“User TT”, ‘WW HC tracker’[Name],
“Approach”, ‘WW HC tracker’[Approach],

"Date", 
 --  FIRSTDATE('Date-Snapshot'[Date]),
  DATE( LEFT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 4 ), RIGHT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 2 ), 1 ),

"Total Hours", [@Total Hours],
"Working Hours for Uncapped", [@Working Hours],
"Working h",

    VAR _DiffStart =
        DATEDIFF (
            [@Start date],
            DATE ( LEFT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 4 ), RIGHT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 2 ), 1 ),
            MONTH
        )
    VAR _DiffEnd =
        IF (
            [@End date]
                = DATE ( LEFT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 4 ), RIGHT ( 'Date-Snapshot'[YEAR_MONTH_NBR], 2 ), 1 ),
            TRUE (),
            FALSE ()
        )
        
    RETURN
        SWITCH (
            TRUE (),
            _DiffEnd = TRUE (), BLANK (),
            _DiffStart <= 0, BLANK (),
            _DiffStart = 1
                && [@Total Hours] <= [@Working Hours] * .2, [@Working Hours] * .2,
            _DiffStart = 1
                && [@Total Hours] > [@Working Hours] * .2
                && [@Total Hours] <= [@Working Hours] * .65, [@Total Hours],
            [@Working Hours] * .65
        ),
"Start date", [@Start date],
"End date", [@End date]

)

2

Hi there,
Your dax is hard read in my opinion. If you can format your dax, and make comments on what your dax is trying to accompish. This might help on where your problem is. Leaving space between the error message and start of your dax.

thanks
Keith

3

Here is the formatted Dax:

% Customer time =
VAR _temp =

    FILTER (
        SUMMARIZECOLUMNS (
            'WW HC tracker'[Name],
            'WW HC tracker'[Approach],
            'Date-Snapshot'[YEAR_MONTH_NBR],
            FILTER (
                'Date-Snapshot',
                'Date-Snapshot'[Date]
                    = FIRSTDATE ( 'Date-Snapshot'[Date] )
                    && 'Date-Snapshot'[Date] < TODAY ()
            ),
            "@Total Hours", [Customer time (hours)],
            "@Working Hours", [FY working hours YTD],
            "@Start date",
                COALESCE (
                    SELECTEDVALUE ( 'WW HC tracker'[Start date] ),
                    CALCULATE (
                        MAX ( 'WW HC tracker'[Start date] ),
                        REMOVEFILTERS ( 'Date-Snapshot' )
                    )
                ),
            "@End date",
                COALESCE (
                    SELECTEDVALUE ( 'WW HC tracker'[End date] ),
                    CALCULATE (
                        MAX ( 'WW HC tracker'[End date] ),
                        REMOVEFILTERS ( 'Date-Snapshot' )
                    )
                )
        ),
        NOT ISBLANK ( [@Total Hours] )
    )
)

RETURN
SELECTCOLUMNS (
_temp,
“User TT”, ‘WW HC tracker’[Name],
“Approach”, ‘WW HC tracker’[Approach],
“Date”,
– FIRSTDATE(‘Date-Snapshot’[Date]),
DATE ( LEFT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
4
), RIGHT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
2
), 1 ),
“Total Hours”, [@Total Hours],
“Working Hours for Uncapped”, [@Working Hours],
“Working h”,
VAR _DiffStart =
DATEDIFF (
[@Start date],
DATE ( LEFT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
4
), RIGHT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
2
), 1 ),
MONTH
)
VAR _DiffEnd =
IF (
[@End date]
= DATE ( LEFT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
4
), RIGHT (
‘Date-Snapshot’[YEAR_MONTH_NBR],
2
), 1 ),
TRUE (),
FALSE ()
)
RETURN
SWITCH (
TRUE (),
_DiffEnd = TRUE (), BLANK (),
_DiffStart <= 0, BLANK (),
_DiffStart = 1
&& [@Total Hours] <= [@Working Hours] * .2,
[@Working Hours] * .2,
_DiffStart = 1
&& [@Total Hours] > [@Working Hours] * .2
&& [@Total Hours] <= [@Working Hours] * .65, [@Total Hours],
[@Working Hours] * .65
),
“Start date”, [@Start date],
“End date”, [@End date]
)