Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello first time poster here. Been searching for a solution to my problem for several hours and was hoping you could help me.
I have a situation where I want the user to be able to select which values to display in a matrix visual, by selecting value(s) in a slicer and getting the result in 1 box in the matrix.
I have an EmployeeTable that has all the data and looks like this:
Employee | Week | Weekday | Work Order | Comment | Function |
Employee A | 43 | Monday | 331133 | XY | Driver |
Employee A | 43 | Wednesday | 331113 | TYYY | Admin |
Employee A | 43 | Thursday | 444133 | XLEA | Operator |
Employee B | 43 | Tuesday | 419033 | POE | Driver |
Employee B | 43 | Wednesday | 321031 | FLE | Driver |
Employee F | 43 | Friday | 302131 | FAE | Admin |
Employee F | 43 | Saturday | 493019 | MOE | Operator |
I have a SlicerTable that looks like this:
Info |
Work Order |
Comment |
Function |
I want the user to select any combination from these three values and getting the result in a matrix visual.
If the user selects Work Order they get this result:
Employee | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Employee A | 331133 | 331113 | 444133 | ||||
Employee B | 419033 | 321031 | |||||
Employee F | 302131 | 493019 |
If they select Work Order and Comment they get this result:
Employee | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Employee A | 331133 XY | 331113 TYYY | 444133 XLEA | ||||
Employee B | 419033 POE | 321031 FLE | |||||
Employee F | 302131 FAE | 493019 MOE |
If the user selects Comment and Function they should get this result:
Employee | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
Employee A | XY Driver | TYYY Admin | XLEA Operator | ||||
Employee B | POE Driver | FLE Driver | |||||
Employee F | FAE Admin | MOE Operator |
I've started writing some DAX expressions, starting out with 1 or 2 of the columns.
I have so far:
Measure =
IF(COUNTAX(FILTER(SlicerTable;SlicerTable[Info]="Work Order");SlicerTable[Info])>0;
CONCATENATEX(
Measure2=
Both of these measures seem to yield the same result, i.e. that they show the value regardless of the outcome of the IF. So for Measure i get the matrix with the Work Order values in every cell. For Measure 2 I get Work Order and Comment in every cell.
HOWEVER, when I create a card visual and select a cell in the matrix visual, the card displays the correct result.
Does anyone have any insight into either how to write a dax expression, or enlighten me as to why the card visual displays correct information when I click on a cell in the matrix, but the matrix itself does not?
Appreciative for any support
Solved! Go to Solution.
@eriksjoo , Create a measure like this and use
measure =
var _1 = countx(filter(SlicerTable, SlicerTable [Value] ="Work Order"),SlicerTable [Value])+0
var _2 = countx(filter(SlicerTable, SlicerTable [Value] ="Comment"),SlicerTable [Value])+0
var _3 = countx(filter(SlicerTable, SlicerTable [Value] ="Function"),SlicerTable [Value])+0
return
if(_1 >0 , max(employee[Work Order]),"") & " " & if(_2 >0 , max(employee[Comment]),"") & " " & if(_3 >0 , max(employee[Function]),"")
=var _s=allselected(slicer[column]) return concatenatex(table,if("work order" in _s,table[work order])&if("comment" in _s,table[comment])&if("function" in _s,table[function]),",")
=var _s=allselected(slicer[column]) return concatenatex(table,if("work order" in _s,table[work order])&if("comment" in _s,table[comment])&if("function" in _s,table[function]),",")
Thank you very much Daniel! This also worked
@eriksjoo , Create a measure like this and use
measure =
var _1 = countx(filter(SlicerTable, SlicerTable [Value] ="Work Order"),SlicerTable [Value])+0
var _2 = countx(filter(SlicerTable, SlicerTable [Value] ="Comment"),SlicerTable [Value])+0
var _3 = countx(filter(SlicerTable, SlicerTable [Value] ="Function"),SlicerTable [Value])+0
return
if(_1 >0 , max(employee[Work Order]),"") & " " & if(_2 >0 , max(employee[Comment]),"") & " " & if(_3 >0 , max(employee[Function]),"")
Thank you so much! This worked brilliantly! Saved my night 🙂
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |