Power BI Measures and Slicers

[Anonymous]

Hi! I have a table in power BI which has 4 columns Name, Date, Type and Quantity.
Requirement: I have two slicers in the report for Date and Name. And I need to create a measure which calculates the sum of quantities making sure that only one slicer(i.e.,date) applies on it.
Issue: I tried almost all the formulas for it :- ALL, ALLSELECTED, ALLEXCEPT but none of them seems to work. It works as per the date, but as soon as I select a name in the another slicer the value changes, I want the measure to work according to only date slicer and name filter should not have any effect on it. I cant use "edit interactions" as I will be using this measure for the calculation of another measure.
Example: Suppose we have this table and we have two slicers in the report: one for Date and one for Name. I have to create a measure that calculates the total quantities on the basis of dates selected. The Name slicer should not have any effect on it. I have to use this measure further in other measures, so I cannot use edit interactions.
Date	Name	Type	Quantity
01-May-18	Dhruv	AB	100
01-May-18	Dhruv	AB	200
01-May-18	Dhruv	BC	100
01-May-18	Aahan	BC	300
01-May-18	Aahan	CD	400
04-May-18	Kanika	CD	100
04-May-18	shreya	CD	200
27-Jun-18	kartik	CD	100
Suppose I have selected date range as 1st May to 4th May and Dhruv in the Name slicer. The measure should show the value as : 1400 but instead it shows 1100. It excludes all those dates where Dhruv is not falling under.
Kindly help in getting the desired result.

[v-juanli-msft]

Hi @Anonymous 

Create a table without relationship with your table

date table = CALENDARAUTO()

Create a measure

Measure = CALCULATE(SUM('Table'[Quantity]),FILTER(ALL('Table'),'Table'[Date]<=MAX('date table'[Date])&&'Table'[Date]>=MIN('date table'[Date])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,

This solution works for me but what if I want my date slicer to also work for other visuals(that are depending on the the main table).As there is no relationship between date table and the main table, I can see in screenshots attached by you, that date slicer has no affect on the Total Quantity.

For me, I have created the summarized table from the main table that has date column and the 'sum of quantity' on that date.Please see that summarized table below:

Then I created the relationship between this summarized table and main table('Test' on my case). I am using the Date column of that summarized table on my date slicer.This solution has solved all the problem.

[Anonymous]

Have you tried:

AllFromDateRange = 
CALCULATE(
    SUM('Table'[Quantity]),
    ALLEXCEPT('Table', 'Table'[Date])
)

This will sum everything, only keeping the Date Filter.  I've assumed your slicer is set to the date column of 'table'.  If not. substitute the same table/column used in the slicer.

[Stachu]

have you tried this:

Measure = 
CALCULATE(
    SUM('Table'[Quantity]),
    ALL('Table'[Name])
)

[Anonymous]

"ALL" is not working for me.

I want to create a measure to calculate one person's % to the Total people. All people are in one slicer and date in another. I would like my card visual to show % of quantity of one person to total in a given time period but still need the measure for my Total quantity to stay the same even when I filter on Name slicer. 

For some reason I can't figure out how to write the code such that I can select one person and still have my Total quantity measure be the same. It should not change even if we select any name. 

[Stachu]

EDIT I just noticed that you posted that question in the Power query forum, not DAX. Are you trying to use DAX in the Query Editor? It doesn't seem consistent with what you wrote about the slicers, but it would explain why ALL (or anything written in DAX) doesn't work for you

In what sense ALL doesn't work? Does it give an error, or the result is different than what you expect?
Please compare these 2 and post a screenshot with and without slicer selection:

Measure 1 = 
CALCULATE(
    SUM('Table'[Quantity]),
    ALL('Table'[Name])
)

Measure 2 = SUM('Table'[Quantity])

Is this table the only one in your model? If not what are the other tables, and what joins do they have with this table?

Are the slicers based on the table you posted or one of the joined tables?