Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
Regards,
Xiaoxin Sheng
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |