Filter a slicer with another slicer using a measure

[ErisedAlurem]

I have seen a lot of examples about what I'm trying to achieve, but they don't seen to apply for my problem. I have two different tables, "clientes_distinct_view" and "sistemas".

The first one has a list of clients and their internal code for the system they use (normally they use more then one system), each column is related to one specific system. The second table is a list of all systems. There is no direct relation between the two tables.

For example: Client X has a system code for Y and Z; looking in the cliente_distinct_view table, you'll see a column with the client_name, code_Y and code_Z; the other system code (code_A, code_B, code_C... are empty for this client).

I have created two slicers, one with all clients and other with all systems; when I select a specific system, I'd like the client slicer to show only the clients with a non-blank code_system (for the specific system).

How can I achieve this?

The examples I have seen always use some kind of measure that evaluates to 0 or 1, and then they apply a logic based on that. The problem is, I would like to make this logic only valid according to what system is been filtered, and I don't know how to use the information I have to achieve this.

For example, if I tried filtering using the logic (if code_Y <> BLANK()), when I try to filter based on code_Z, or if I don't use any filters at all, it would remove a lot of clients that I need to show.

I imagine that some sort of switch statement would do the trick, but I have no idea how to make it return a single value, and not a list. Something like:

--create a variable that verifies what system is been filtered (it only works for single selection)
VAR verifier = if (isfiltered(lista_sistemas[Sistemas]),FIRSTNONBLANK(lista_sistemas[Sistemas],lista_sistemas[Sistemas]),FALSE())
if (verifier = "Z",
filter(clientes_distinct_view,clientes_distinct_view[system_Z]<>BLANK(),
if(verifier = "Y",
filter(clientes_distinct_view,clientes_distinct_view[system_Y]<>BLANK(),
)
)

Obviously this doesn't evaluate to a single value, so I can't create a measure like this.

Any ideas would be appreciated. If more information is required, please let me know and I will make my best to provide it.

Here is an example of the tables, to make it easier to visualize.

cliente_distinct_view:

Client	code_Y	code_Z	code_W	code_F
6326			084091
55365			084176
55372	84383		023825
55385		89162	89162
55387		89164	89164
63239	23318	23318		23318
63249	41501	41501		41501
63325	11698	11698		11698
63710	50999	13855		50999

Sistemas:

System

system_Y

system_Z

system_W

system_F

PS:(I cannot use bidirectional relationships).

[rohit_singh]

Hi @ErisedAlurem ,

The easiest way to do it is to transform your source tables slightly using Power Query to match your front end requirements. Please follow the steps below :

1) Load your tables into Power Query 

Table 1 : cliente_distinct_view

Table 2 : Sistemas

In the following steps, we will modify both tables in order to create a relationship between them in the report view.

2) In table cliente_distinct_view , select the column "Client" and then go to "Transform" --> "Unpivot columns" --> "Unpivot other columns". This will modify the structure of the tables, wherein the code headers will now be rows.

3) Next, add a new column named "Code" to the table by splitting column "Attribute" on the "_" delimiter. This will give you a column of codes.

4) In table Sistemas, add a new column named "Code" by spliitting column "System" on the "_" delimiter. This will give you a column of codes similar to what's on the other table.

5) In the report view, we will now use the "Code" column that we created on both tables to craete a relationship between the two tables as shown below.

6) Once this is done, add the field "Client" to a table vsual, and "System" on a slicer. You will now see a filtered list of clients as per the selected system value.

This is the final result :

M-code for reference :

cliente_distinct_view

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY9NDsUgCISvYlx3Ib/CWYz3v0ZprT7egswMfCEwRlVCrVctXzXj5vCGeY0qQir/Y+iaxx0jGZPRRpAMJSO2N5iDYtIf0RPBSRcRN5JHQiKwpOX4j+KHYpAGScvxi6L3OgB1S1qOX1SHFh1p7s9OiCf2F6s35w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, code_Y = _t, code_Z = _t, code_W = _t, code_F = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", Int64.Type}, {"code_Y", Int64.Type}, {"code_Z", Int64.Type}, {"code_W", Int64.Type}, {"code_F", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Code", each Text.AfterDelimiter([Attribute], "_"), type text)
in
    #"Inserted Text After Delimiter"

Sistemas

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKq4sLknNjY9UitWBc6KQOeHIHDel2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [System = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"System", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Code", each Text.AfterDelimiter([System], "_"), type text)
in
    #"Inserted Text After Delimiter"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[ErisedAlurem]

Thanks for your reply, I didn't think of that. Just one question, is there a way to make it without creating duplicates for client column? I need it to be single values because of some relationships I have in my report.

[rohit_singh · Accepted solution]

Hi @ErisedAlurem ,

If you don't want to affect other relationships because of change in granularity, one option is to create a table by reference in Power Query, and use that to create a relationship with the sistema table.

You can use the original untransformed "cliente_distinct_view" table as the source, and create another table , say "cliente_unpivoted_view" . This will keep the original table intactand the duplication will only be on the referenced table.

let
    Source = cliente_distinct_view,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Client"}, "Attribute", "Value"),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Code", each Text.AfterDelimiter([Attribute], "_"), type text)
in
    #"Inserted Text After Delimiter"

Then create a relationship between cliente_unpivoted_view and sistema to get the required data.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊