Forum Discussion
Using two smart filters
Hello there, apologies if this is a simple question, I am new to DAX and Power Bi. The purpose of my code is to show gross profit. I have three filters on the dashboard, which should adjust the gross profit readings. When the filters are applied individually, the gross profit is adjusted correctly. However, when using multiple filters, the original gross profit is calculated (with no filters).
New GP = VAR SelectedPersonsGP = CALCULATE(CALCULATE(SUM(Append1[Gross Profit]),ALL(Append1[TLDESC ])),ALLSELECTED(Append1[Person]))
VAR NonSelectedPersons = CALCULATE(SUM(Append1[Gross Profit]),ALL(Append1[Person]),ALL(Append1[TLDESC ]))
VAR PersonFilter = IF(ISFILTERED(Append1[Person]),NonSelectedPersons-SelectedPersonsGP,NonSelectedPersons)
VAR SpecificFilter = IF(ISFILTERED(Append1[TLDESC ]),CALCULATE([One Off Expenses],ALL(Append1[Person])))
VAR NewGP = PersonFilter+SpecificFilter
RETURN
NewGP
Any help is appreciated, many thanks.
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/
6 Replies
I will try and clarify my post:
The dashboard filters enable me to search for the individual and 'add' (the value is negative) their potential GP to the total GP, for example if they were working on a separate project and were excluded. I would also like to add one off expenses (specific filter) to the GP. As the data for these inidivuals do not overlap, I have used separate if statements using all filters and tried to sum the resulting variables. When using both filters however, the original GP is returned (Non specific person), perhaps to do with the order of the filters?
I would really appreciate it if someone could help me. Thanks
can you share the sample from Append1 table?
is NonSelectedPersons supposed to show all sales rather than sales for non selected? I must say I find the naming quite confusing when comparing with syntax
what is in Append1[TLDESC ] and why it requires ALL criteria?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
SelectedExpenseswhen I ask for the table I really need the structure - can you copy say top10 rows with anonymized data when necessary?
