How to Use ALL or ALLEXCEPT with RELATED table columns as filters

[Anonymous]

Hi,

I wonder if you can help. I am looking to create a bar chart that displays information from 2 tables... table 1 and table 2, the x axis being the "month-year" of a date look up table.

The data from table 2 is a simple count but from table 1 I want to layer on conditions to be a count for only two statuses.

I can't seem to figure out the allexcept for a related table.. the below is what I have got so far. Can anyone shed some light on this?

=CALCULATE([Number Of Learners],

filter(ALLEXCEPT(Table1, 'Calendar'[Month-year]),

                     OR('Table1'[Status] = "Achiever",'Table1'[Status] = "Cancelled")))

[dax · Accepted solution]

Hi PollyHalton50,

Did you want to get below result?

You could try below measure(create a relationship between two tables)

Measure =
CALCULATE (
    COUNT ( 'Table'[status] ),
    FILTER ( ( 'Table' ), 'Table'[status] = "a" || 'Table'[status] = "b" )
)

Best Regards,
Zoe Zhi

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