CALCULATE with removefilters

[swatibehl]

Hello,

I have requirement where in Card has to compute reliability as CalculateFailed/CalculateTotal .(Both Failed and Total are measures)

Slicer has below filters-

Errortype and Timestamp.

Calculate Failed Measure requires the Slicer filteration , however, CalculateTotal measure does not require the Errortype filter . 

I have create another measure to test the results with removefiters but it is not returning correct values

Slicer is build from  Table[ErrorType]

CalculateTotal Measure  = COUNTX(FILTER('Table', 'Table'[Operation]="Create"), 'Table'[ID])

CalculateFailed Measure = COUNTX(

    FILTER(

        'Table',

        'Table'[State] = "Failed" && Table[Operation] = "Create"),'Table'[Id])

Test Measure =

   VAR A =CALCULATE([CalculateTotal],REMOVEFILTERS(Table[ErrorType]))

        VAR B = CALCULATE([CalculateFailed],REMOVEFILTERS(Table[ErrorType]))

        RETURN A

VAR A is considering Errotype Slicer values passed from Slicer, not sure why is it so. Can anyone pleaase suggest

[Anonymous · Accepted solution]

Hi @swatibehl 

Based on your question, we guess that you may have an impact on filtering because you have table relationships.

In the method we provide, "all" only removes the filter of "error type" in the current table. If you have an “error type” in table B, there is a relationship between the two tables, and your slicer selects “error type” in table B, the same will have an impact.

You can try removing the "error type" to avoid the above reasons

Test Measure = 
    VAR TotalWithoutErrorType = CALCULATE([CalculateTotal], ALL('Table'))
    VAR FailedWithAllFilters = [CalculateFailed]
    RETURN TotalWithoutErrorType

Best Regards,

Nono Chen

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

[Anonymous]

Hi @swatibehl 

You seem to want to calculate CalculateFailed/CalculateTotal, and CalculateTotal is not affected by slicers, here is the DAX we offer:

Here is the virtual table we created

The "CalculateTotal" and "CalculateFailed" measures you defined seem to be correct.

CalculateTotal = COUNTX(FILTER('Table', 'Table'[operation]="create"), 'Table'[id])

CalculateFailed = COUNTX(
    FILTER(
        'Table',
        'Table'[state] = "fail" && 'Table'[operation] = "create"),'Table'[id])

The modified Test Measure is as follows, and after the slicer is applied, Total is not affected

Test Measure = 
    VAR TotalWithoutErrorType = CALCULATE([CalculateTotal], ALL('Table'[error type]))
    VAR FailedWithAllFilters = [CalculateFailed]
    RETURN TotalWithoutErrorType

Create a measure to calculate reliability, here are the results

result = [CalculateFailed] / [Test Measure]

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

 

Best Regards,

Nono Chen

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

[swatibehl]

Hello @Anonymous , thanks for taking time and helping .. 

I have tried the same details.. but when I  am returning Var for total wherein filter is not needed , is returning same result as that of applying filter. I am really going clueless why is this so

Var A returning 7 for Measure wherein slicer is not needed and is still in effect( even  though ALL is used)

Var B , for Failed measure is also returning same result with slicer is in effect and is good

[Dangar332]

HI, @swatibehl 

try below

CalculateTotal Measure  = 
   COUNTX(
      FILTER(
         all('Table'[errortype],'Table'[Operation],'Table'[ID]),
         'Table'[Operation]="Create"
      ),
      'Table'[ID]
   )

 

CalculateFailed Measure = 
   COUNTX(
       FILTER(
        all('Table'[errortype],'Table'[Operation],'Table'[ID],'Table'[State])
        'Table'[State] = "Failed" && Table[Operation] = "Create"
       ),
     'Table'[Id]
   )

[swatibehl]

thanks for your kind reply.. i have tried with suggested option , but no luck.do you have any running sample code ?

[123abc]

The issue you're experiencing is due to the way REMOVEFILTERS works in conjunction with the context of calculations in DAX. When you use REMOVEFILTERS(Table[ErrorType]), it removes the filters from the ErrorType column, but not from other columns or measures that may be affected by those filters.

Let's try to address your problem step by step.

1. CalculateTotal Measure: Since this measure doesn't need the ErrorType filter, it remains as it is.

2. CalculateFailed Measure: This measure needs to have the ErrorType filter removed when calculating. However, using REMOVEFILTERS as you've done may not work as expected because the context might still propagate from other parts of your model.

3. Test Measure: You're trying to calculate the total and failed measures without the ErrorType filter.

A potential solution could involve using the ALLSELECTED function combined with your calculations. Here's how you can modify the Test Measure:

Test Measure =
VAR TotalWithoutErrorTypeFilter =
CALCULATE(
[CalculateTotal],
ALLSELECTED('Table'[ErrorType])
)

VAR FailedWithoutErrorTypeFilter =
CALCULATE(
[CalculateFailed],
ALLSELECTED('Table'[ErrorType])
)

RETURN
TotalWithoutErrorTypeFilter

Here, ALLSELECTED('Table'[ErrorType]) will remove the filter from ErrorType while retaining other filters (like the timestamp). This way, when you calculate your totals and failed counts, it will ignore the ErrorType filter but still respect other filters.

Ensure that you adjust the formula as per your model's specific requirements, but this approach should help you achieve the desired result.