Row sum in matrix with boolean value

[v-yalanwu-msft]

Hi, @Anonymous ;

Sorry, your model calculation will be very complicated, and you may need to make auxiliary tables. Your mode is SSAS, right? You cannot create auxiliary columns?

Best Regards,
Community Support Team_ Yalan Wu

[v-yalanwu-msft]

Hi. @Anonymous ;

I'm very sorry, I can't reproduce your model, and can't judge the reason. Can you share the file and hope the output result after removing sensitive information?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

According to the official document, This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules. 

There is no mention of the restrictions in SSAS, maybe because of the version, you can try to update the version of the desktop. In addition, I tested it with SSAS and it was ok.

Secondly, you can also use HASONEVALUE or ISFILTERED functions to replace ISINSCOPE function , Another point is to create measure instead of column.

Or can you share me Screenshot of the error message.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi， @Anonymous ;

You could create two measures about it:

count = IF(SUM([value])>0,1,0)

measure =
IF (
    ISINSCOPE ( 'Table'[name] ),
    SUMX ( FILTER (ALL ( 'Table' ),
            [name] = MAX ( 'Table'[name] )
                && DATEDIFF ( [Date], MAX ( 'Table'[Date] ), WEEK ) IN { 0, 1, 2, 3 }),
        [count]),
    SUMX (FILTER (ALL ( 'Table' ),
            DATEDIFF ( [Date], MAX ( 'Table'[Date] ), WEEK ) IN { 0, 1, 2, 3 }),
        [count] ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi， @Anonymous ；

You could create a measure by ISINSCOPE() AND HASONEVALUE().

Measure = IF(HASONEVALUE('Table'[Date])&&ISINSCOPE('Table'[name]), IF(SUM([value])>0,1,0),SUM('Table'[value]))

The final output is shown below:

If this formula does not apply to your data, can you share a simple example similar to your original table structure and the result you want to output?

Looking forward to your reply！

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.