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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Dhruvan
New Member

DAX

How to get all values based on selected values for example : if we select "150" then we need to get all related values with "15" using DAX 

1 ACCEPTED SOLUTION

Hi @Dhruvan, here is DAX solution. However I personally prefer to go for "Text Filter" visual suggested by @mlsx4 . Good luck!

Sergii24_0-1694608085170.png

 

Partial search measure = 
VAR _FullSearchTerm = SELECTEDVALUE( 'Selected value'[Column1] )                //here we capture selected value, remember that only 1 value at a time should be selected, otherwise use iferror argument of SELECTEDVALUE()
VAR _NumberOfCharactersToSearch = 11                                            //change number to change number of characters you're interested in
VAR _RealSearchTerm = LEFT( _FullSearchTerm, _NumberOfCharactersToSearch )      // Assumption: first N left characters

RETURN 
    IF(
        CONTAINSSTRING( 
            SELECTEDVALUE( 'Related value'[Column1] ),
            _RealSearchTerm
        ),
        1,
        0
    )

 

View solution in original post

9 REPLIES 9
Dhruvan
New Member

Thanks to all, it worked successfully.👍

Perfect! Remember to mark a preffered answer as a solution to help others easily find it 🙂

mlsx4
Super User
Super User

Hi @Dhruvan 

 

Can you share an example of what you have as data and what to do you try to achieve?

Hi @mlsx4 ,

Selected value part - 500-453674-599

Related value part - 500-453674-600,500-453674-601,500-453674-588,500-453674C599,500-453674D597.

So when select the mentioned value(500-453674-599) ,we should get all the values related to it as shown in the example.

explain in detail and post a normal example

Selected value part - 500-453674-599

Related value part - 500-453674-600,

                                 500-453674-601,

                                 500-453674-588,

                                500-453674C599,

                                500-453674D597.

So when select the mentioned value(500-453674-599) ,we should get all the values (The rows that matching to Prefix of the selected value)related to it as shown in the example.

 

do you want to filter the column or get the whole value as one row or what?

Hi @Dhruvan, here is DAX solution. However I personally prefer to go for "Text Filter" visual suggested by @mlsx4 . Good luck!

Sergii24_0-1694608085170.png

 

Partial search measure = 
VAR _FullSearchTerm = SELECTEDVALUE( 'Selected value'[Column1] )                //here we capture selected value, remember that only 1 value at a time should be selected, otherwise use iferror argument of SELECTEDVALUE()
VAR _NumberOfCharactersToSearch = 11                                            //change number to change number of characters you're interested in
VAR _RealSearchTerm = LEFT( _FullSearchTerm, _NumberOfCharactersToSearch )      // Assumption: first N left characters

RETURN 
    IF(
        CONTAINSSTRING( 
            SELECTEDVALUE( 'Related value'[Column1] ),
            _RealSearchTerm
        ),
        1,
        0
    )

 

Hi @Dhruvan 

 

You can import from the store text filter visual. It does exactly what you need:

 

mlsx4_0-1694605871218.png

 

mlsx4_1-1694605943664.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors