Forum Discussion

SDPowerBI123's avatar
SDPowerBI123
Regular Visitor
1 year ago
Solved

selectedvalue dax question

i have a simple what if parameter generating a list of numbers like this:

Numbers of Months = GENERATESERIES(3, 26, 1),
 
and then i have a selectedvalue dax measure that captures what is being selected like this:
Numbers of Months Value = SELECTEDVALUE('Numbers of Months'[Numbers of Months])
 
i put this measure in a card, and link the column to a slicer, when i selected lets say 15, the card with the measure inside says 15 no problem.
 
the problem is when i try to use this selectedvalue dax in another measure like this one:
Open X Months Prior =
var PriorDate = DATEADD('O 29 Years Monthly'[Date], -'Numbers of Months'[Numbers of Months Value], MONTH)
RETURN
CALCULATE(
    SUM('O 29 Years Monthly'[Open]),
    ALL('O 29 Years Monthly'),
    'O 29 Years Monthly'[Date] = PriorDate
)
 
so long story short i am trying to do some data analysis on some stock, and i try to discount the months back, i assume that measure would work in that second measure, but it just returns blank. can any one help me to understand why its returning blank when the card clearly says its returning a number? thanks.
 
i put link below.
 
Need Help
Tips and Tricks
  • connect's avatar
    connect
    1 year ago

    Hi there, 

     

    You need to understand that SELECTEDVALUE () DAX function returns a scalar value while DATEADD () Returns a table. Also, pay attention to the filter context over here. 

    Lets understand it a bit futher!

    Why It Returns Blank?

    The DATEADD function, along with other time intelligence functions, expects a column with dates as input, not a scalar value like what you get from SELECTEDVALUE. When you pass -‘Numbers of Months’[Numbers of Months Value] directly to DATEADD, it's possible that the context is not correctly understood, resulting in an invalid operation and hence returning blank.

    What can you do as follows?


    Modify the Measure
    Open X Months Prior =
    VAR SelectedMonths = SELECTEDVALUE('Numbers of Months'[Numbers of Months]) // Get the selected number of months
    VAR PriorDate =
    CALCULATETABLE(
    DATEADD('O 29 Years Monthly'[Date], -SelectedMonths, MONTH),
    ALL('O 29 Years Monthly')
    )
    RETURN
    CALCULATE(
    SUM('O 29 Years Monthly'[Open]),
    FILTER(
    ALL('O 29 Years Monthly'),
    'O 29 Years Monthly'[Date] IN PriorDate
    )
    )

    I hope this will work for you!! 

5 Replies

  • connect's avatar
    connect
    Resolver I
    1 year ago

    Hi there, 

     

    You need to understand that SELECTEDVALUE () DAX function returns a scalar value while DATEADD () Returns a table. Also, pay attention to the filter context over here. 

    Lets understand it a bit futher!

    Why It Returns Blank?

    The DATEADD function, along with other time intelligence functions, expects a column with dates as input, not a scalar value like what you get from SELECTEDVALUE. When you pass -‘Numbers of Months’[Numbers of Months Value] directly to DATEADD, it's possible that the context is not correctly understood, resulting in an invalid operation and hence returning blank.

    What can you do as follows?


    Modify the Measure
    Open X Months Prior =
    VAR SelectedMonths = SELECTEDVALUE('Numbers of Months'[Numbers of Months]) // Get the selected number of months
    VAR PriorDate =
    CALCULATETABLE(
    DATEADD('O 29 Years Monthly'[Date], -SelectedMonths, MONTH),
    ALL('O 29 Years Monthly')
    )
    RETURN
    CALCULATE(
    SUM('O 29 Years Monthly'[Open]),
    FILTER(
    ALL('O 29 Years Monthly'),
    'O 29 Years Monthly'[Date] IN PriorDate
    )
    )

    I hope this will work for you!! 

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi SDPowerBI123 ,

    Modifying your measure to ensure that the PriorDate is calculated in a context where [Numbers of Months Value] has a defined value and is used correctly will resolve the issue of the blank card value.

    Open X Months Prior =
VAR SelectedMonths = [Numbers of Months Value]
VAR PriorDate = 
    CALCULATE(
        MAX('O 29 Years Monthly'[Date]),
        DATEADD('O 29 Years Monthly'[Date], -SelectedMonths, MONTH)
    )
RETURN
CALCULATE(
    SUM('O 29 Years Monthly'[Open]),
    FILTER(
        ALL('O 29 Years Monthly'),
        'O 29 Years Monthly'[Date] = PriorDate
    )
)

     Best regards,

      • Irwan's avatar
        Irwan
        Super User
        1 year ago

        hello SDPowerBI123 

         

        i dont find any issue with your DAX.

        'Open X Months Prior' is a column and the value is shown inside the column (i assumed the value is expected to be right), so if your DAX would show no value or error value.

         

        the only thing that i could find is, you dont create relationship between 'Number of Months' table and 'O 29 Years Monthly'.

         

        You take month number in your slicer from 'Number of Months' table, while you use 'Open X Months Prior' as your value in bar chart. However there is no relationship between those two table.

         

        first i create a calculated column to get month number in 'O 29 Years Monthly'.

        then i use that number as a relationship between those two table.

        and your calculation is shown automatically.

         

        However, this is just an example because the calculated column that i made is not matched to your calculation.

         

        Basically the issue might be from relationship so you need to seek where is the relationship between those two tables.

         

        Hope this will help.

        Thank you.