Help with measure

[badger123]

Hello,

Hoping someone can help with this. I have tried to simplify my problem with some dummy tables (see pbix file here: https://drive.google.com/file/d/1emMTSkXIrs0D8ZIjVURqC0yLf_70Ma3k/view?usp=sharing). 

I have a table with items and terms - items can have overlapping terms in which case the term is duplicated. I am using items as a slicer (from table 3) to work in an exclude manner, so if an item is selected (e.g. item two) then all item two terms are excluded (see measure 1). The values are then summed for the remaining terms (see measure 2).

Measure1 = CALCULATE(COUNT('Table3'[Term ID]), CROSSFILTER(Table3[Term ID], Table1[Term ID], both))

Measure 2 = IF(isblank([Measure1]),SUM(Table2[Values]), BLANK())

What I am trying (and failing) to do is to apply this logic to other calculations without having the term field in the table visual. Specifically, how can I sum up the values against items based on the terms that haven't been excluded / selected. For example, based on selecting item two in the slicer, this is what I would like to achieve in a table visual:

Item	Values
item one	3930
item three	2550

I've been trying to make a measure using summarize, filter, but have had no luck! Any advice appreciated!

[v-shex-msft]

Hi @badger123 ,

AFAIK, power bi slicers not support 'exclude' mode. You can refer to following steps to achieve exclude filter effect.
Steps:
1. Turn off relationship from 'table 3' to 'table 1'.
2. Write a measure to compare between records and summarize related values:

Exclude measure =
VAR _exIDList =
    EXCEPT (
        CALCULATETABLE ( VALUES ( Table1[Term ID] ), ALLSELECTED ( Table1 ) ),
        VALUES ( Table3[Term ID] )
    )
RETURN
    CALCULATE (
        SUM ( Table2[Values] ),
        FILTER ( ALLSELECTED ( Table2 ), Table2[Term ID] IN _exIDList ),
        VALUES ( Table1[Term] )
    )

Notice: if you not turn off relationship, your records will been filtered by auto exist function.

Understanding DAX Auto-Exist

Regards,

Xiaoxin Sheng

[badger123]

Amazing, thanks @v-shex-msft . That works. Do you know how I can sum the values broken down by date fromTable 2? I.e. achieve the following (based on selecting item two in slicer):

Date	Values
Jan-19	1450
Feb-19	1180
Mar-19	1300

[v-shex-msft · Accepted solution]

Hi @badger123 ,

I don't think you can direct use above formula on your scenario.

You need to change relationship to break auto exist filter, then use current date to filter on correspond team id from other table and use extract id list as filter condition.

Regards,

Xiaoxin Sheng