Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

Filter a Table based on Column 1 contains "Text1" and Column 2 contains "Text2"

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

 

OrderIDOrderDateRegionRepItemUnitsUnit Cost
11/6/16EastJonesPencil951.99
21/23/16CentralKivellBinder5019.99
32/9/16CentralJardinePencil364.99
42/26/16CentralGillPen2719.99
53/15/16WestSorvinoPencil562.99
64/1/16EastJonesBinder604.99
74/18/16CentralAndrewsPencil751.99
85/5/16CentralJardinePencil904.99
95/22/16WestThompsonPencil321.99
2 ACCEPTED SOLUTIONS
waltheed
Solution Supplier
Solution Supplier

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?

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

View solution in original post

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 )
    )
    

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
waltheed
Solution Supplier
Solution Supplier

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?

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

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 )
    )
    

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @waltheed,@Phil_Seamark

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hey, you are the Author of "Beginning DAX with Power BI", I didn't realize the first time.
I can't believe you replied to my query and I already have your paper book but I haven't unpacked it, I bought it in Jan this year but I also have "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo and once I am done with that I will practice with your book.
I am from Excel,VBA and SQL background and now I just want to learn DAX for Power BI, I have learned a lot from these books and the community.
Thanks once again.

Hi @iamprajot

 

That's cool.  I hope you like the book.  When did it arrive?  I think it is still quite new.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yeah and I don't know when it arrived but it was available in India at that time, I think end of Feb and not Jan, sorry.
It is also available online at Amazon India for a long time now, here is the link, https://www.amazon.in/Beginning-DAX-Power-BI-Intelligence/dp/1484234766?tag=googinhydr18418-21&tag=g...

Got it, new perspective of filter I see now, thanks.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.