Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I want to Filter this Sample Data and get the SUM/AVERAGE of whatever...
Problem is filtering the columns based on the containing alphabets.
Filter condition 1, Region Contains or Start with "C"
Filter condition 2, Item Contains or Start with "P"
I tried filtering if Region Starts With "C" and Item Starts With "P", which worked.
CALCULATE (
AVERAGE ( Data[Units] ),
FILTER ( Data, LEFT ( Data[Region], 1 ) = "C" && LEFT ( Data[Item], 1 ) = "P" )
)
but what if "C" and "P" are in middle and not the starting characters?
Sample Data is
OrderID | OrderDate | Region | Rep | Item | Units | Unit Cost |
1 | 1/6/16 | East | Jones | Pencil | 95 | 1.99 |
2 | 1/23/16 | Central | Kivell | Binder | 50 | 19.99 |
3 | 2/9/16 | Central | Jardine | Pencil | 36 | 4.99 |
4 | 2/26/16 | Central | Gill | Pen | 27 | 19.99 |
5 | 3/15/16 | West | Sorvino | Pencil | 56 | 2.99 |
6 | 4/1/16 | East | Jones | Binder | 60 | 4.99 |
7 | 4/18/16 | Central | Andrews | Pencil | 75 | 1.99 |
8 | 5/5/16 | Central | Jardine | Pencil | 90 | 4.99 |
9 | 5/22/16 | West | Thompson | Pencil | 32 | 1.99 |
Solved! Go to Solution.
You can use the FIND function for that.
For example:
Column = find("e", Customer[CompanyName],1,blank())
Finds the E in the customer name and returns the position iof the first occurrence.
So in your case:
CALCULATE (
AVERAGE ( Data[Units] ),
FILTER ( Data, FIND("C",Data[Region]>0 && FIND("P",Data[Item]>0 )
)
Does that help?
HI @iamprajot
Please try using this slightly modified version
CALCULATE ( AVERAGE ( Data[Units] ), FILTER ( Data, FIND("C",Data[Region],1,0) >0 && FIND("P",Data[Item] ,1,0) >0 ) )
You can use the FIND function for that.
For example:
Column = find("e", Customer[CompanyName],1,blank())
Finds the E in the customer name and returns the position iof the first occurrence.
So in your case:
CALCULATE (
AVERAGE ( Data[Units] ),
FILTER ( Data, FIND("C",Data[Region]>0 && FIND("P",Data[Item]>0 )
)
Does that help?
HI @iamprajot
Please try using this slightly modified version
CALCULATE ( AVERAGE ( Data[Units] ), FILTER ( Data, FIND("C",Data[Region],1,0) >0 && FIND("P",Data[Item] ,1,0) >0 ) )
Both the solutions provided are good except @waltheed missed the closing bracket.
But I am still wondering how a Boolean condition can filter a column based on a specific alphabet.
FILTERING Data FOR FIND("C",Data[Region],1,0) >0
If someone could explain a little to me ?
I am closing this as solution.
Thanks again.
HI @iamprajot
The FILTER function will by default return all rows from the Data table that end up wtih a true value for the combination of the boolean tests. When the filter condition (in this case using FIND(....) > 0 ) returns a false, the row is not returned.
Hi @iamprajot
That's cool. I hope you like the book. When did it arrive? I think it is still quite new.
No, filter function needs actual filtering arguments that exist in the column filtered, it does not accept Boolean (True/False) statements.
So it will not work.
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |