Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I need the line in chart to change as per multiple slicer selections.I am using line and stacked column chart. X axis will have count of Classification . now i have removed in the graph below.
I am halfway through, its getting changed as per two slicer (Year and BU) but not the third one(Classification) and only shows up if that year is selected.
my Target line for 2021 should be as below if , Year 2021 and BU selection but no Classification is selected. (which i am able to achieve)
"AA"= 0.85,
"CC" =0.63,
"CA"= 0.86,
"GF"= 0.35,
if no BU selected 0.66
Now if any classification is selected along with year which is (lost time accident or Restricted work) the values should change as below
"AA"=0.31,
"CC"=0.31,
"CA"= 0.38,
"GF" = 0.15,
No BU selected 0.27
My DAX formula:
Test 2021 =
IF(SELECTEDVALUE('Calendar'[Year])=2021,
SWITCH (
SELECTEDVALUE ( vw_dart_fte[New BU] ),
"AA", 0.85,
"CC", 0.63,
"CA", 0.86,
"GF",0.35,
0.66
),(IF(SELECTEDVALUE('Calendar'[Year])=2021 && (SELECTEDVALUE(vw_dart_lta[classification])="Lost Time Accident") || (SELECTEDVALUE(vw_dart_lta[classification])= "Restricted work"),
SWITCH(SELECTEDVALUE(vw_dart_fte[New BU]),
"AA", 0.31,
"CC", 0.31,
"CA", 0.38,
"GF",0.15,
0.27
))))
This the relationship between three tables. there is no Common column between dart_lta and dart_fte so i created a column which combination of year, month number and BU for there relationship. and with month number and year created a date column in both tables so that they can be conncetd to Calendar table.
so here is screenshot of my graph..where you can see, if the classification is selected it still shows value of 0.66 but it should show 0.27. so all the values it showing up are of 1st condition where no classisfication is selected
here it should show 0.38
Can someone help me in this?
Thankyou in advance
Solved! Go to Solution.
Hi @snaraya
Please try
Test 2021 =
VAR Year2021 =
SELECTEDVALUE ( 'Calendar'[Year] ) = 2021
VAR SelectedBU =
SELECTEDVALUE ( vw_dart_fte[New BU] )
RETURN
IF (
Year2021,
IF (
ISFILTERED ( vw_dart_lta[classification] ),
SWITCH (
SelectedBU,
"AA", 0.31,
"CC", 0.31,
"CA", 0.38,
"GF", 0.15,
0.27
),
SWITCH (
SelectedBU,
"AA", 0.85,
"CC", 0.63,
"CA", 0.86,
"GF", 0.35,
0.66
)
)
)
Hi @snaraya
Please try
Test 2021 =
VAR Year2021 =
SELECTEDVALUE ( 'Calendar'[Year] ) = 2021
VAR SelectedBU =
SELECTEDVALUE ( vw_dart_fte[New BU] )
RETURN
IF (
Year2021,
IF (
ISFILTERED ( vw_dart_lta[classification] ),
SWITCH (
SelectedBU,
"AA", 0.31,
"CC", 0.31,
"CA", 0.38,
"GF", 0.15,
0.27
),
SWITCH (
SelectedBU,
"AA", 0.85,
"CC", 0.63,
"CA", 0.86,
"GF", 0.35,
0.66
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
19 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |