Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to filter properly?

Edited per Greg_Deckler suggestion. Thank you Hello, I have a report that I am attempting to provide the users with two seamlingly simple a simple [Count] and [Modified Count] measues. The [Count...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Assuming the answer to the previous question is "Yes" the modified code is below. Essentially you had it correct except that you cannot recalculate a variable, which is what you were trying to do. This is why you were getting the incorrect result.

     

    ModifiedCount = 
    VAR Metric = SUM('f_primary'[Count])
    VAR HasOneExpression = 
        OR ( 
            HASONEVALUE ( 'f_primary'[Field.1] ), 
            HASONEVALUE ( 'f_primary'[Field.2] ) 
        )
    VAR CalcHasOneFilter = 
        CALCULATE ( 
             SUM('f_primary'[Count]), 
            ALLEXCEPT ( 'f_primary', 'f_primary'[Field.1], 'f_primary'[Field.2], 'DateTbl'[Date] )
        ) 
    VAR CalcAll = CALCULATE ( SUM('f_primary'[Count]), ALLEXCEPT(f_primary, DateTbl[Date]))
RETURN
    IF ( HasOneExpression, CalcHasOneFilter, CalcAll )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for the suggested posting guidelines. I attempted to modify my post to make this more clear. I have searched around for this answer in the community and have watched many youtube/sqlbi clips and cant figure out what I am doiung wrong. Thank you for any assistance you are able to provide!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous Thanks for the edits. Taking a look now. Is your RETURN line supposed to read:

     

    RETURN IF ( HasOneExpression, CalcHasOneFilter, CalcAll )
     
    ?
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Anonymous - Assuming the answer to the previous question is "Yes" the modified code is below. Essentially you had it correct except that you cannot recalculate a variable, which is what you were trying to do. This is why you were getting the incorrect result.

       

      ModifiedCount = 
    VAR Metric = SUM('f_primary'[Count])
    VAR HasOneExpression = 
        OR ( 
            HASONEVALUE ( 'f_primary'[Field.1] ), 
            HASONEVALUE ( 'f_primary'[Field.2] ) 
        )
    VAR CalcHasOneFilter = 
        CALCULATE ( 
             SUM('f_primary'[Count]), 
            ALLEXCEPT ( 'f_primary', 'f_primary'[Field.1], 'f_primary'[Field.2], 'DateTbl'[Date] )
        ) 
    VAR CalcAll = CALCULATE ( SUM('f_primary'[Count]), ALLEXCEPT(f_primary, DateTbl[Date]))
RETURN
    IF ( HasOneExpression, CalcHasOneFilter, CalcAll )
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you sir! I will test this out in a bit and see how it works. I knew i shouldnt have done that with the recalculated measure. I originally did that because I have to recreate this measure for other calculations and thought I could get by with copy/pasting the calculation and just changing the metric used at the top. I'll test and come back if this actually works to 'mark as solution'. Thank you