Use Disconnected Table Slicer to select columns to dynamically show in matrix visual

[Richard_Halsall]

Hi

After research I am struggling with the following

I have a disconnected table named Client Role which I am using as a slicer with fields Role|CertName

I have a Fact table named FactTechnicianCertificates which stores technicians and the certificates they hold with fields Contractor__c|Certificate_Name__c|End_Date__c
I have a Dim table named DimContractor which stores technician data with fields Contractor__c|First Name|Last Name

Fact & Dim joined on Contractor__c|

A certificate could appear in more than 1 client role

Certificates can be held and not be associated to a client role

Simplified sample data for each table but in the real world there are at least 20 client roles with up to 20 associated certificates per role

Client Role

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVghJTc7Iy0zOTMxT0lFyTi0qUTA0UIrVwSlphkfS2BIs6erm7oFNKz5JI3ySxvgkTfDaCXRuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Role = _t, CertName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Role", type text}, {"CertName", type text}})
in
#"Changed Type"

FactTechnicianCertificates

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZQ7j8IwEIT/CkqN5N31I3EZ4KRDaUh1BaJAHB0vobv7/Wc7QQpJNnYsF1t8Gs2MH/t9dgSqJfiFq8pW2/IrW2br8/NngeAmkAJQEJDJDstJ2rS08rSK0NJ2tXWEVugmCYlOFHltTNTW2k348h2j80CTgDwlpfUNohVg37TVZ6Cvx9/bpS7Lt76HKTnajKZk6ND3MCWnPRqSgWlORulhJcCkKCsPu4RFko3xk+GkiynXj9NG5XX50e0DiHHdh30fEhkffdj3QS8bMTj0AcLtBLi5H0YgJknDLCN6jhNNU07u37vTX9d3+HWKtmwZo0NKy9zrPt38OoZ5MwNtnKONuqd9+Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contractor__c = _t, Certificate_Name__c = _t, End_Date__c = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contractor__c", type text}, {"Certificate_Name__c", type text}, {"End_Date__c", type date}})
in
#"Changed Type"

DimContractor

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjQwCjQ2AAFDJ29Lb0/HcCUdJZfEslQgFZybWZKhFKsDVmXiAVaVm1ialxPo6AiU9spPzANSPvl56WiKCpJdTMwDHV1Bspk5OZVgxXmpxWjK8lMCkssgynwTi7KBVEBqSWoRUFksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contractor__c = _t, #"First Name" = _t, #"Last Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contractor__c", type text}, {"First Name", type text}, {"Last Name", type text}})
in
#"Changed Type"

I am using the Disconnected table field Role as a slicer which when a role is selected I want to show a matrix similar to below BUT only with those columns where the certificate is relevant to the role as detailed in the Client Role table
e.g. for role ABC Technician matrix would have 3 certificate columns, for role EFGH Technician matrix would have 5 certificate columns

The row is DimContractor|Last Name and value is FactTechnicianCertificates|End_Date__c

Any help would be appreciated. Thanks

[v-tangjie-msft · Accepted solution]

Hi @Richard_Halsall ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Measure = 
var a=VALUES('Client Role'[CertName])
return CALCULATE(MAX(FactTechnicianCertificates[End_Date__c]),FactTechnicianCertificates[Certificate_Name__c] in a)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.