Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everybody and thanks in advance for your assistance.
I have a single table called Staff that includes data from a Multi-Option Select. EG
ID | Name | Team Options |
12314 | Ed Mercer | Deals Direct With Friend |
12345 | Kelly Grayson | Deals Direact With Friend; Deals Direct With Third Party |
23453 | Bortus | Deals Direct With A Pal; Deals Direct With Third Party |
234324 | Claire Finn | Tends to Others |
234234 | John Lamarr | Deals Direct With Friend; Tends to Others |
324324 | Gordon Malloy | Deals Direct With Friend; Deals Direct With a Pal, Deals Direct With Third Party; Tends to Others |
34323 | Alara Kitan | Deals Direct With Third Party; Tends to Others |
2433214 | Talla Keyali | |
343245 | Isaac | Deals Direct With A Pal; Deals Direct With a Third Party |
Using the delimiter (semicolon), I split apart the Team Options column into a new column called Team Options List (creating additional rows in the Staff table), then created a slicer with the following choices from Teams Ooptions List:
(Blank) |
Deals Direct With Friend |
Deals Direct With A Pal |
Deals Direct With Third Party |
Tends to Others |
I would like the table to return ONLY the values selected in the slicer.
EG:
1. Selecting nothing would return Talla Keyali.
2. Selecting Deals Direct With Friend and Deals Direct With Third Party would only return Kelly Grayson and Isaac.
3. Selecting all except (Blank) would only return Gordon Malloy
4. Selecting only Deals Direct With Friend would only return Ed Mercer
I've seen other examples that use Concatenatex and I can get a measure with a list in the correct order that matches the Team Options in the Staff Table, but I can't use the results of that measure to filter the table.
Here's the measure I created:
I can see the results of the measure coming out to show what I want, but I am hamstrung at this point. I've tried other suggestions but it hasn't been successful, so I would like advice on how to filter the table.
Can you please help me?
Thank you again!
Solved! Go to Solution.
Hi @MisterWolf
Please refer to attched sample file with the solution.
I would create a seperate filter table (normally using power query) however, can also be created using DAX. This way I wouldn't have to double or tripple the number of rows of my staff table.
This the fllowing measure can be placed in the filter pane of the table visual, select "is not blank" and apply the filter.
Hi @MisterWolf
Please refer to attched sample file with the solution.
I would create a seperate filter table (normally using power query) however, can also be created using DAX. This way I wouldn't have to double or tripple the number of rows of my staff table.
This the fllowing measure can be placed in the filter pane of the table visual, select "is not blank" and apply the filter.
I'm relatively new at this and am having difficulty.
I'm not sure where to select "is not blank" - when I try to create the Teams Options List table like you did above, I get a "the arguments in GenerateSeries function cannot be blank" error.
Thank you for your patience. Hopefully I can learn this and then apply it moving forward.
That seems to work! When I run the slicer, I appear to get the correct count and records! Thank you!
I will go over everything you did and research it so I can understand exactly how it works!
Thanks again!
Thank you! I was considering a separate filter table and I am glad you mentioned it.
It probably won't be for a day or two, but I appreciate your prompt response and will let you know what happens!
Thanks again!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |