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
"- I am using the hasonevalue function to identify when Field.1 and Field.2 are selected." To check if something has been selected from a column, it's best to use ISFILTERED, not HASONEVALUE. Actually, to see if only one value ahs been selected, you should use a combination of the two functions above.

And then again... Please read this to see why you should not do what you do above: https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Best
D
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you Anonymous. I will check that out today as well. Apparently my search was not inclusive enough. I really need to finish their DAX Masterclass...