Forum Discussion

badger123's avatar
badger123
Resolver I
7 years ago
Solved

Help with measure

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:

 

ItemValues
item one3930
item three2550

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      badger123
      Resolver I
      7 years ago

      Amazing, thanks Anonymous . 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):

       

      DateValues
      Jan-191450
      Feb-191180
      Mar-191300

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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