Forum Discussion

ssspk's avatar
ssspk
Icon for Helper III rankHelper III
1 year ago

Interactive filter for the Grouped keywords to ungrouped keywords in screen

Hello Team,

I have a 2 Matrix component,

One with Grouped Keywords and UnGrouped keywords.

Grouped keywords are in the format of ,

 

Here Table1 to Table 2 relationship is Many to One. For columns term(table1) to group(table2).

As per the image below if i click the word 'salary' search term it has to display the Ungrouped Table data proper data.(interactive filter).

How to create a relationship for the column terms(table2) here with which column and table?

How to do it?

Table: table1 - Ungrouped keywords

datetermcount
Monday, January 27, 2025salary2
Wednesday, December 25, 2024Salary3
Monday, January 27, 2025salarY HR1
Monday, January 27, 2025trainm2
Tuesday, February 4, 2025working time4
Tuesday, December 10, 2024work time2
Tuesday, December 13, 2024traiNM2

 

Table: table2 - Grouped keywords

groupcountterms
hr salary and statement6salary, salary, Salary,Salary,Salary,salarY HR
trainm4trainm,trainm,traiNM,traiNM
working time6working time,working time,working time,working time,work time,work time

 

 

 

 

 

 

 

 

 

 

 

Thank you,

Siva

 

2 Replies

  • Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • I think I understand your problem.

     

    The main answer is that the relationship between table1 and table 2 is many to many, not many to one / one to many. Your model should actually look like this:

     

     

     

    You also have a 'gotcha' lurking, which is that DAX is case insensitive, but it looks like you want your terms to be case sensitive (e.g., for trainm group, you want to see count of terms split between trainm and traiNM).

     

    In summary, there are two main things we want to do, both of which must be done in Power Query:

    1. Construct our bridge table, calling it "table3 - Bridge" from table2
    2. Update table1[term] and table3[terms] to be "case sensitive" (using quotes as, again, we are implementing a somewhat hacky workaround, not truly making case sensitive)
    3. Finally, I'll briefly cover how to use the bridge table, specifically to respond to your cross-filtering request

    Step 1: Let's actually tackle #2 first. Since we want to apply the same transformation across different queries/tables, let's define a custom function to use. Create a new blank query, rename query to "FauxSensitize", go to advanced editor, replace with the below:

     

    FauxSensitize

    ( inputTable as table, optional ColumnNames as list ) as table =>
    let
        tb = inputTable,
    
        //If ColumnNames is null/unspecified, then get list of ALL text column names
        cols = ColumnNames ?? Table.SelectRows( Table.Schema( tb ), each [Kind] = "text" )[Name],
    
        //Define replacement list to convert 'lower char' to 'lower char + invisible char'. 
        //Needs to be in format { {'lower char', 'lower char + invisible char' }, {...,...}, ... }
        origFixedPair =
        let
            origLower = {"a".."z"},
            fixedLower = List.Transform( origLower, each _ & Character.FromNumber(8203) )
        in
            List.Zip( { origLower, fixedLower } ),
    
        //Construct the 'function' and 'output type' components of the {column name, function, output type} 
        //structure that Table.TransformColumns expects
        transformFuncAndOutputType = {
            (startText) => 
            if startText = null then null else
            let
                charList = Text.ToList( startText ),
                fixedChars = List.ReplaceMatchingItems( charList, origFixedPair )
            in
                Text.Combine( fixedChars ),
            type nullable text
        },
        
        //Combine cols and transformFuncAndOutputType to get complete transforms list
        transforms = List.Transform( cols, each { _ } & transformFuncAndOutputType )
    in
        Table.TransformColumns( tb, transforms )

     

    We'll invoke this function in our queries later.

     

    Step 2: Next, let's create the bridge table we need. Similar to above, create a new blank query, rename to "table3 - Bridge", open advanced editor, and replace with the following:

     

    table3 - Bridge

    let
        Source = #"table2 - Grouped keywords",
        SelectGroupTerms = Table.SelectColumns(Source,{"group", "terms"}),
        DistinctSplitAndTrimmedTerms = 
        Table.TransformColumns(
            SelectGroupTerms, 
            {{
                "terms", 
                each List.Distinct( List.Transform( Text.Split( _, "," ), Text.Trim ) ), 
                type {nullable text}
            }}
        ),
        ExpandTerms = Table.ExpandListColumn(DistinctSplitAndTrimmedTerms, "terms"),
        SensitizeTerms = FauxSensitize( ExpandTerms, {"terms"} ) //<--- invoking custom function
    in
        SensitizeTerms

     

     

    Step 3: Final small but important step to take in Power Query is to also apply our FauxSensitize function to table1[term] as mentioned at the top. The values in table3[terms] and table1[term] must match exactly as that's how we are relating the two tables. Easiest way to do this is probably to 'Insert Step After' on last step in Applied Steps and enter the following in the formula bar:

     

    = FauxSensitize( #"<Insert Name of Previous Step>", {"term"} )

     

    Step 4: Apply your changes and exit Power Query. You now want to set up the relationships as I shared above. Another view of relationships to create:

     

     

    Step 5: You will now want to leverage the bridge table in applicable measures that you author. See here if you aren't too familiar with working with many-to-many relationships: https://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

     

    As an example, and to now achieve what you originally asked (have a matrix of table2 cross-filter a matrix of table1), we can define a simple measure using 'table3 - Bridge' and set it as a filter on our table1 matrix.

     

    First, let's create said measure.

     

    BridgeFilter = IF( NOT ISEMPTY( 'table3 - Bridge' ), 1 )

     

    Add this measure to your table1 filter and set to 'is not blank'. Your table1 matrix will look like this.

     

     

     

    And your table2 matrix will be the same (no bridge filter needed):

     

     

    The table2 --> table1 cross filtering should now work: