Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
duncfair
Advocate II
Advocate II

Why is this DAX suddenly returning nothing?

I inherited this report and am not DAX fluent. [OrderFunnelAmt] has values, so I am at a total loss here.
Please let me know if and what else I need to troubleshoot this.
 
SelectedOrdFun =
VAR Mth = [SelectedKPI_Month]
VAR CY = IF(vw_FiscalCal[IsDecember] = 1, [Current_FY] - 1, vw_FiscalCal[Current_FY])
VAR Qtr = [SelectedKPI_Qtr]
RETURN
    SWITCH (
        [SelectedKPI_Time],
        // FY
        1,
            CALCULATE (
                [OrderFunnelAmt],
                REMOVEFILTERS ( vw_FiscalCal ),
                vw_FiscalCal[FiscalYear] = CY
            ),
        // YTD
        2,
            CALCULATE (
                [OrderFunnelAmt],
                REMOVEFILTERS ( vw_FiscalCal ),
                vw_FiscalCal[FiscalYear] = CY,
                vw_FiscalCal[FiscalMonthofYear] <= Mth
            ),
        // QTD
        3,
            CALCULATE (
                [OrderFunnelAmt],
                REMOVEFILTERS ( vw_FiscalCal ),
                vw_FiscalCal[FiscalYear] = CY,
                vw_FiscalCal[FiscalQuarter] = Qtr,
                vw_FiscalCal[FiscalMonthofYear] <= Mth
            ),
        // MTD
        4,
            CALCULATE (
                [OrderFunnelAmt],
                REMOVEFILTERS ( vw_FiscalCal ),
                vw_FiscalCal[FiscalYear] = CY,
                vw_FiscalCal[FiscalMonthofYear] = Mth
            )
    )
1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @duncfair ,

 

I looked at your dax and according to your description, your DAX returns empty.
For this DAX, it is important to be clear whether you are creating measures or creating new columns. If it is a measure, then whether the "[SelectedKPI_Month]" in your VAR Mth = [SelectedKPI_Month] is another measure, you can't use the column name to get the data here.
For you to return null, my advice to you is that when you define a variable every time, you use return to verify the value of the variable to see if it is caused by a variable calculation error.

 

Best Regards,

Neeko Tang

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

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

Hi @duncfair ,

 

I looked at your dax and according to your description, your DAX returns empty.
For this DAX, it is important to be clear whether you are creating measures or creating new columns. If it is a measure, then whether the "[SelectedKPI_Month]" in your VAR Mth = [SelectedKPI_Month] is another measure, you can't use the column name to get the data here.
For you to return null, my advice to you is that when you define a variable every time, you use return to verify the value of the variable to see if it is caused by a variable calculation error.

 

Best Regards,

Neeko Tang

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

Thanks. I will dig in further, but the variables in question are the selected slicer values on the page.

 

duncfair_0-1667480571082.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors