Forum Discussion

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
5 years ago

Return multiple elements with selectedvalue dax function

I have a report which has Month Unit, Month Sales, Qtr Unit, Qtr Sales, Year Unit and Unit Sales.The report has slicers for Month, Qtr and Year. 

 

The measure created for the Month sales is;

Month sales = CALCULATE([Sales Amount],
FILTER(ALL('Date'),
'Date'[Month Name] = SELECTEDVALUE('Date'[Month Name])))

 

There are 2 issues I face here, since its selectedvalue in the measure it returns value on only one selection, I want it to return values even on multiple selection.
 Secondly I would like to filter the Month Sales and Month Unit by Quarter and Year also.
 Although adding the similar filter in the measure for quarter and year seems to work but it also should work on the similar concept where selecting multiple quarter or years the measure returns the value.
 

 

 

Need Help

4 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Avivek ,

     

    Try to use "IN" and "VALUES" like so:

    Month sales =
CALCULATE (
    [Sales Amount],
    FILTER ( ALL ( 'Date' ), 'Date'[Month Name] IN VALUES ( 'Date'[Month Name] ) )
)

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    WHy not create a date calendar with Year, Quarter and Month as Columns.

    Then link your date calendar to you sales table on Date column.

     

    Then create slicers on the Year / Qrtr / Month column of the date table

     

    It saves you the trouble of working with DAX

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Avivek ,

     

    What about using ALLSELECTED like below:

     

    MONTH SALES =
CALCULATE ( [Sales Amount], ALLSELECTED ( 'Date'[Month Name] ) )

     

    Regards,

  • Payeras_BI's avatar
    Payeras_BI
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Avivek,

     

    Please confirm if this is your expected output:

     

    Let's say you select month JAN and FEB, year 2014 and no selection for Quarter.

     

    Month Sales = Sales for 2014 JAN + 2014 FEB

    Quarter sales = Sales for all 2014 1Q

    Year Sales = Sales for all 2014

     

    Is this what you want? If not detail further.

     

     

    Regards,