Measure with DAX Calculate and miltiple filters

[Draszor]

hi, 

I created a new table (MONTH/YEAR) that contains Date (called Month/Year_1 - formatYYYY-MM) dimension from my main data table (MONTH/YEAR = VALUES(DATA[Month/Year]) ).

 Now I would like to create a measure that would return Amount for marked dates in  Month/Year_1 slicer  from newly created table.

The below formula works very fine but only if single value is selected in Month/Year_1 slicer. 

AMOUNT_1 = CALCULATE(

SUM(DATA[AMOUNT]), DATA[Month/Year]= VALUES('MONTH/YEAR'[Month/Year_1]))

When I choose more than one or none, then I get error "A table of multiple values was supplied while single value expected". How to correct it so that it would work for multiple or none choice done in Month/Year_1 slicer?

[amitchandak · Accepted solution]

@Draszor , if they are related

only SUM(DATA[AMOUNT]) will do

else

AMOUNT_1 = CALCULATE(
SUM(DATA[AMOUNT]), DATA[Month/Year] in  VALUES('MONTH/YEAR'[Month/Year_1]))

or

AMOUNT_1 = CALCULATE(
SUM(DATA[AMOUNT]), DATA[Month/Year] in  allselected('MONTH/YEAR'[Month/Year_1]))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Draszor]

thank you amitchandak, 

the :

AMOUNT_1 = CALCULATE(
SUM(DATA[AMOUNT]), DATA[Month/Year] in  allselected('MONTH/YEAR'[Month/Year_1]))

does its job!!

I can now multichoose. the only issue is that I need to remember that the table (matrix) is showing records having data only for Month/Year selection. So, if in Month/Year_1 data selection there is a record having some value, and this record has no value in Month/Year data selection, I won't see it... some complication I need to remember. 

[v-robertq-msft]

Hi, @Draszor 

According to your requirement, you want to calculate the sum of amount and slice according to year and month.

My suggestion is that you don’t have to create another table for the slice, you just need to slice based on the ‘DATA’[year/month], you can follow my steps:

1. Create a table, and place fields, for [Amount] field, you should set as “sum”, like this:

2. Create a slicer, place the [DATA]’Month/Year’, like this:

3. Then you can select more than one values, and the table can show the correct value, like this:

See, you don’t even have to create a measure, just operate in charts.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Draszor]

Thank you Robert for this solution. 

In fact my questions is a part of bigger whole. if I would like to do just simple filtering - you are right, all the complications with additional dimension is just waist of time. 

What I am really trying to do:

1. I have a table (DATA) full of rimensions, between them is AMOUNT (digits) and Year/Month (YYYY-MM)

2. I would like to create 2 slicers - Year Month and Year/Month_1

3. I would like to create one measure AMOUNT_1 reacting on dates choice in Year/Month_1 slicer but not reacting on any choice being done in Year/Month slicer

4. I would like then to create a matrix, place there couple of dimensions, place there AMOUNT, AMOUNT_1 the difference between them.

5. now, it would be easy to see the figures for two dates slots and the difference between them, and make this dynamic - meaning I can choose the dates in Year/Month and Year/Month_1 based on my needs. 

All those went good, I created another table with Year/Month, renamed the single column of this table to Year/Month_1, I created a measure as described and this measure reacts to choice I make in Year/Month_1 slicer BUT... I am allowed to choose just one date. multi dates or no dates does not work. 

the solution I chose has one weak side - my matrix dimensions are filtered based on the choice done in Year/Month slicer  - meaning, if for matrix combination of dimensions Apple-Green, there is any figure in chosen date of Year/Month, then this combinations is shown irrespectively of existence of any figures in Year/Month_1 date. however if the date chosen in Year/Month gives no results for Apple-Green combination, then even though the figures may exist for date chosen in Year/Month_1, the record is not shown. No idea how to overcome this - any hints?