ISINSCOPE with AllEXCEPT

Hi Anonymous 

Thanks for reaching out to us.

“Is there any other way to not add these tweleve columns in the dax to get the correct values.” Perhaps you can try function all(), it will ignore any filters that might have been applied, e.g. 

CALCULATE (
    DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
    FILTER ( ALL ( 'PFS' ), 'PFS'[column1] <> "value you want to filter" )
)

then the measure will not be impacted by the 12 filters in your report.

Also, you can see what went wrong by returning a single variable.

Category Avg =
VAR p =
    SELECTEDVALUE ( 'CFS'[ClientNumber], BLANK () )
VAR PAvgRating1 =
    CALCULATE ( COUNT ( 'PFS'[Rating] ), 'PFA'[ClientNumber] <> p )
VAR PAvgRating2 =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'PFS'[Category] ),
            CALCULATE (
                DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                'PFS'[ClientNumber] <> p,
                ALLEXCEPT ( 'PFS', 'PFS'[Category] )
            ),
        ISINSCOPE ( 'PFS'[Sub Category] ),
            CALCULATE (
                DISTINCTCOUNT ( 'PFS'[ClientNumber] ),
                'PFS'[ClientNumber] <> p,
                ALLEXCEPT ( 'PFS', 'PFS'[Category], 'PFS'[Sub Category] )
            ),
        1
    )
RETURN
    p
// or PAvgRating1  or PAvgRating2

If you could share some sample data( bogus data), then we can check the measure further.

 

Best Regards,

Community Support Team _Tang

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