Forum Discussion
Using two smart filters
- 8 years ago
check out this thread
https://community.powerbi.com/t5/Desktop/or-logic-slicer/m-p/460738
solution I posted there involves creating new tables, but there is also link to SQLBI article on the matter
https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/
Thanks for the reply, unfortunately I can not share the data as it is company informtation.
Yes, NonSelectedPersons shows the gross profit of the firm as a whole, not highlighting any particular person, it should be named allpersons, apologies.
TLDESC is the description of transaction lines. When filtered to one off expenses using the specific filter, it should add this to the total gross profit (to help with forecasts as these events are one off and can be ignored). It should add this to the gross profit value if it is entered in the filter, regardless of whether a selected person has been filtered in.
Currently, the gross profit value is = NonSelectedPersons when both the person and specific filter are used.
For reference, the One off Expenses code is as follows:
One Off Expenses =
VAR ConsultantExpenses = CALCULATE(SUM(Append1[-Formula5]),FILTER(Append1,Append1[TLNOMCOD]=80000),ALL(Append1[Person]))
VAR AssociateExpenses = CALCULATE(SUM(Append1[-Formula5]),FILTER(Append1,Append1[TLNOMCOD]=80250),ALL(Append1[Person]))
VAR ConsultantRechargedExpenses = CALCULATE(SUM(Append1[-Formula5]),FILTER(Append1,Append1[TLNOMCOD]=70050),ALL(Append1[Person]))
VAR AssociateRechargedExpenses = CALCULATE(SUM(Append1[-Formula5]),FILTER(Append1,Append1[TLNOMCOD]=70250),ALL(Append1[Person]))
VAR SelectedExpenses = CALCULATE(-[Unchargeable expenses],ALLSELECTED(Append1[TLDESC ]),ALL(Append1[Person]))
RETURN
SelectedExpenses
when I ask for the table I really need the structure - can you copy say top10 rows with anonymized data when necessary?
- PowerBi_188 years agoFrequent Visitor
TLDESC Person Gross Profit Conference 1 Person 1 -10 Conference 1 Person 1 -30 Conference 1 Person 2 -15 Conference 1 Person 2 -6 Conference 1 Person 2 -1500 Conference 1 Person 2 -2000 Conference 1 Person 3 -500 Conference 1 Person 3 -100 Conference 1 Person 4 -25 Conference 2 Person 1 -100 Conference 2 Person 2 -60 Conference 2 Person 2 -20 Conference 2 Person 5 -50 Other costs Person 6 -5000 project 1 person 1 25000 project 2 person 4 10000 project 3 person 5 12500 Hi, thanks again for looking at ths. So I've written up a basic tabe of what is happening. If we filtered on Person 6 AND Conference 1, we would want to add the 9186 (sum of conference 1 and person 6) to the total gross profit. In effect, seeing what the result would be without one off costs (conference) and without person 6 being busy.
- Stachu8 years agoCommunity Champion
check out this thread
https://community.powerbi.com/t5/Desktop/or-logic-slicer/m-p/460738
solution I posted there involves creating new tables, but there is also link to SQLBI article on the matter
https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/