Forum Discussion

Dhruvan's avatar
Dhruvan
New Member
2 years ago
Solved

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 
Data Protection
  • Sergii24's avatar
    Sergii24
    2 years ago

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

     

    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
    )

     

Dhruvan's avatar
Dhruvan
New Member
2 years ago

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.

 

Sergii24's avatar
Sergii24
Super User
2 years ago

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

 

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
    )