measure fileter by 2 values, one in measure another in slicer

[mmm286b]

Hi,

I need to have 3 cards Each one have a measure like these:

Medida 2 = 
CALCULATE (
    AVERAGE ( TablaConTotal[Jobs] ),FILTER ( ALL ( TablaConTotal ),  [SERVER] = "SERVERA"
     )
)

The difference betwen 3 measures is the Server.

Then I would like to have a slice where I can select the year to apply these measure in the card.

I'm trying it  but I see that if I put the filter command in the measure the slice don't works.

Any help? Thanks

[v-jianpeng-msft · Accepted solution]

Thank you danextian , dk_dk and anmolmalviya05 

Hi, @mmm286b 

Based on your description, I have the following sample data:

You can choose from the following two DAX metric expressions to complete your calculations:

Measure = 
CALCULATE(AVERAGE('Table'[Jobs]),FILTER(ALLEXCEPT('Table','Table'[Year]),'Table'[SERVER] = "SERVERA"))

Measure1 = 
CALCULATE(AVERAGE('Table'[Jobs]),FILTER(ALL('Table'),'Table'[SERVER] = "SERVERA" && 'Table'[Year] IN VALUES('Table'[Year])))

Here are the results:

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

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

[danextian]

Hi @mmm286b 

I am assuming that the year is coming from the same table TablaConTotal. ALL(TablaConTotal) removes any existing filters on the TablaConTotal table and ensures the FILTER function evaluates the condition across all rows in the table, regardless of any active filters in the current context that is why your year filter doesnt work. 

Try this:

Medida 2 = 
CALCULATE (
    AVERAGE ( TablaConTotal[Jobs] ),FILTER ( TablaConTotal,  [SERVER] = "SERVERA"
     )
)

[dk_dk]

Hi @mmm286b 

Why do you need the ALL ( TablaConTotal ) condition? You most likely need to modify the filter conditions in your CALCULATE to "allow" the years to filter the card again through the slicer. Could you provide a sample of your data tables, the relationships between them, and what columns you are using in the slicer?

[anmolmalviya05]

Hi @mmm286b, Please try below measure:

Medida 2 =
CALCULATE(
AVERAGE(TablaConTotal[Jobs]),
TablaConTotal[SERVER] = "SERVERA"
)