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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Gondal-756
New Member

Unable to use SELECTEDVALUE variable in DAX calculations

Hi,
I have a DAX calculation where I use a slectedvalue()  from a month slicer.
If I just return the selectedvalue() itself,  it returns the selected month from slicer, but when I try to use this selectedvalue in a DAX condition, it never works.


My Data:

1. Calendar Table :

Gondal756_1-1713715534641.png


2. Sales Table:

Gondal756_2-1713715660392.png



What I Want:


Sum the sales for each month , but only upto the selected month (in month slicer).
For example if I select February, I should see total sales of January and February only.

Gondal756_3-1713715827399.png

My current DAX Code :

Sales by Selected Month = 
VAR SelectedMonth = SLECTEDVALUE('Calendar'[date].[Month])

RETURN
CALCULATE(
           SUM('Table'[Sales(USD)]), 
           FILTER(
                 'Table',
                  'Table'[date].[Month] <= SelectedMonth
                  )
 )   


In the above Dax code, in the calculate function instead of using SlectedMonth variable, if I use a static month number like 2, it works but when I use the variable it returns the sum of sales for the whole year.


@all








 



1 ACCEPTED SOLUTION

Hi @Gondal-756 

 

It wasn't easy to make the slicer based on the calendar table AND have the visual display rows that AREN'T from the selected month.

 

I based the rows on the values in the table that was originally set up as a disconnected slicer. (renamed to 'MonthList')

 

The measure is as follows:

 

xInnerCalc = 
VAR _Dcurr = MAX( 'Date'[YearMonth] )
VAR _Curr = SELECTEDVALUE( 'MonthList'[YearMonth] )
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[YearMonth],
            "__Amt",
                SUM( 'Sales'[Sales(USD)] )
        ),
        [YearMonth] = _Curr
    )
VAR _Total =
    SUMX(
        _Table,
        [__Amt]
    )
VAR _Result =
    IF(
        _Curr <= _Dcurr,
        _Total
    )
RETURN
    _Result

 

 

To handle the totals, I created the following measure.

 

xSales = 
    SUMX(
        'MonthList',
        [xInnerCalc]
    )

 

 

Let me know if this helps.

 

Gondal-756 - 2.pbix

 

View solution in original post

3 REPLIES 3
Gondal-756
New Member

Hi @gmsambornt Thanks a lot, for your efforts.  

I have following questions:

 

1. Is it must to have a separate slicer table? Can I just use the Date table for slicer directly?

2. In my dashboard, I would use the month slicer for other visuals as well. Since this is a different application of slicer, I can't keep the slicer interaction enabled with this visualization. I want to use the selected slicer month value indirectly using Dax.

In your solution, when I disable the interaction of slicer with visualization, nothing works and it shows all the data. Can we somehow make it work while keeping the interaction disabled?

Gondal756_0-1713734236945.png

 




  

Hi @Gondal-756 

 

It wasn't easy to make the slicer based on the calendar table AND have the visual display rows that AREN'T from the selected month.

 

I based the rows on the values in the table that was originally set up as a disconnected slicer. (renamed to 'MonthList')

 

The measure is as follows:

 

xInnerCalc = 
VAR _Dcurr = MAX( 'Date'[YearMonth] )
VAR _Curr = SELECTEDVALUE( 'MonthList'[YearMonth] )
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[YearMonth],
            "__Amt",
                SUM( 'Sales'[Sales(USD)] )
        ),
        [YearMonth] = _Curr
    )
VAR _Total =
    SUMX(
        _Table,
        [__Amt]
    )
VAR _Result =
    IF(
        _Curr <= _Dcurr,
        _Total
    )
RETURN
    _Result

 

 

To handle the totals, I created the following measure.

 

xSales = 
    SUMX(
        'MonthList',
        [xInnerCalc]
    )

 

 

Let me know if this helps.

 

Gondal-756 - 2.pbix

 

gmsamborn
Super User
Super User

Hi @Gondal-756 

 

In my solution, I used a date table as well as a disconnected slicer table.  

 

1. Create a well-formed Date table.  The date table you listed isn't a date table.  (Even if you aren't using time intelligence calculations, it is a best practice to use a well-formed date table.)

 

In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

  • All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. 
  • There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
  • The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date.

2. Make sure your ‘Sales’[Date] has a Date data-type, not DateTime.  If you need the time portion, duplicate the DateTime column and change to Date.  Rename the columns accordingly.

 

3. Create a 1:* (one direction) relationship between ‘Date’[Date] and ‘Sales’[Date].

 

4. Create a table that is a list of [MMM-YY] and [YearMonth] from ‘Date’ to be used in a slicer.  ( [Month] and [MonthNo] could be used if you prefer.  My preference would be to handle multiple years.)

 

 

SlicerMonths = 
    SUMMARIZE(
        'Date',
        'Date'[MMM-YY],
        'Date'[YearMonth]
    )

 

 

5. Create the following measure:

 

Sales by Selected Month = 
VAR _SelectedMonth = MAX( 'SlicerMonths'[YearMonth] )
RETURN
    CALCULATE(
        SUM( 'Sales'[Sales(USD)] ),
        FILTER(
            ALL( 'Date'[YearMonth] ),
            'Date'[YearMonth] <= _SelectedMonth
        )
    )

 

 

Let me know if you have any questions.

 

Gondal-756.pbix

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors