Dynamic measure based on multiple slicers

[Anonymous]

Hi - 

I have what should be a super simple problem.

Data example:

Person	Hours_Worked	Day_Week
A	5	Monday
B	10	Monday
A	5	Tuesday
C	12	Monday

I have multiple slicers, one for Person and one for Day_week

Example1 - Day_Week slicer set to 'All' , Person slicer set to 'A' - I want to return 10 for 'Hours_Worked

Example2 - Day_Week slicer set to 'Monday' , Person slicer set to 'A' - I want to return 5 for 'Hours_Worked

Currently using: MeasureName = calculate(sum(tableName[Hours_Worked], ALLEXCEPT(TableName,TableName(Person))

The above measure returns the correct sum of hours by person, but doesnt update when the Day_week slicer is used.

PLEASE HELP!

[amitchandak]

@Anonymous ,

Your requirement should be done with

= calculate(sum(tableName[Hours_Worked])), Not sure on the need of all except

Also refer this :https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

[Anonymous]

Thanks - but I need to return the following when 'All' is selected:

Person	Hours_Worked	Day_Week
A	10	Monday
A	10	Tuesday

and the following when a single day (i.e., monday) is selected:

Person	Hours_Worked	Day_Week
A	5	Monday

Reason beig that I need to use this measure in a conditional formatting rule to highligh when hours go above a certain level

[Anonymous]

Hey @Anonymous ,

Try Using 

calculate(sum(tableName[Hours_Worked], ALLEXCEPT(TableName,TableName[Person], ALL(TableName,TableName[Day_Week])

This will give you the sum of Hours for all days (irrespective of what Day you choose in the slicer) for each Person.

Hope this helps!

[karun_r]

@AnonymousWith the table that @Anonymous has, your query will have same result as @amitchandak's query. Correct me if I am wrong.

@Anonymous  I don't think it is possible to do what you are asking with one single metric. For Power BI, it doesn't matter if it's a slicer or a line item in a row. If the measure definition needs it to slice across a value (Day_of_Week in this case), it will slice across it. You can probably have one measure that calculates the Total hours irrespective of the Day_of_Week and one that calculates a regular SUM. Please let us know if you have found a way to achieve this with one measure.

[Anonymous]

@karun_r hmm, perhaps i'm not explaining clearly-

What i'm trying to do is no different than getting the sum of work days, by person and by weekday- in a dynamic fashion - when a slicer is selected

[karun_r]

I am quite not sure on what you mean by dynamically here. Maybe an example diagram in Excel ?

[Anonymous]

dynamic as in, the sum of hours changes based on what's selected within the slicer