Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure with filter in DAX

Dear ,

 

Could you please advise for the formula of DAX in measure below?

 

Context: I want that when I filter the value of week in a slicer, the matrix with column is the week and value (is result from a measure % Accuracy) should be displayed only the selected weeks.

 

Issue: The current measure returns value is 100% for weeks not selected.

 

If I select a week in slicer is week 30.2021 => It should disappear value of week 20.2021->28.2021. But in this matrix, the value still exists with 100% (The correct value is not 100%).

 

 

 

Formual for measure:

% Accuracy = if(
and(sum('RMP Manual SharePoint'[Demand-BW (Qty)])=0, sum('RMP Manual SharePoint'[Total requirement (W. - W.)])=0),
1,
divide(sum('RMP Manual SharePoint'[Demand-BW (Qty)]),sum('RMP Manual SharePoint'[Total requirement (W. - W.)])))
 
Appreciate your support!
 
 

 

 

 

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous Perhaps:

    % Accuracy = 
    VAR __SelectedWeek = MAX(SlicerTable[Week])
    VAR __CurrentWeek = MAX(Table[Week])
RETURN
    if(__SelectedWeek = __CurrentWeek,
      if(
        and(sum('RMP Manual SharePoint'[Demand-BW (Qty)])=0, sum('RMP Manual SharePoint'[Total requirement (W. - W.)])=0),
        1,
        divide(sum('RMP Manual SharePoint'[Demand-BW (Qty)]),sum('RMP Manual SharePoint'[Total requirement (W. - W.)]))
      ),
      BLANK()
  )

    Seems like you ought to also be able to solve this by using the same column for your slicer as you do for your columns in your matrix? The current measure is returning 100% because you return a 1 in your TRUE case. You could also adjust that test condition to account for figuring out if the week is the chosen week. I did this in a nested if statement. Probably might want to consider switching to SWITCH(TRUE()...)