topic Re: Filter _ slicer _ line chart in DAX Commands and Tips

Filter _ slicer _ line chart

Anonymous — Wed, 22 Dec 2021 14:57:47 GMT

Hi Guys,

having this table:

Title	Period	Mileage per period
Incident name	1	1000
Incident name2	1	1000
Incident name3	2	1503
Incident name4	3	1111
Incident name5	4	4503
Incident name6	4	4503
Incident name7	4	4503

I need to get column " incident per period value "

" incident per period value " = 1000 / 2

as there are 2 incidents (or 2 rows for period 1 ) so value is 500

Title	Period	Mileage per period	incident per period value
Incident name	1	1000	500
Incident name2	1	1000	500
Incident name3	2	1503	1503
Incident name4	3	1111	1111
Incident name5	4	4503	1501
Incident name6	4	4503	1501
Incident name7	4	4503	1501

AND

put that into interactive visualisation but with filter on Title.

I want filter on Title that will change values in the graph.

As picture shows below:

after deselection of "Incident name6" for Period 4 it shows value 3302

but I would like to see 2251.5

kind of filter doing:

4503 / 2 = 2251.5

divide 4503 by number of Titles in period 4 BUT without "Incident name6" as it has been deselected on slicer

Incident name5	4	4503	1501
~~Incident name6~~	4	~~4503~~	~~1501~~
Incident name7	4	4503	1501

Is it possible in Power bi?

Re: Filter _ slicer _ line chart

smpa01 — Wed, 22 Dec 2021 15:25:48 GMT

Anonymous this wil do the trick; pbix is atatched

Measure = DIVIDE ( CALCULATE ( MAX ( tbl[ Mileage per period] ), ALLEXCEPT ( tbl, tbl[Period] ) ), CALCULATE ( COUNT ( tbl[Period] ), ALLEXCEPT ( tbl, tbl[Period] ) ) )

Re: Filter _ slicer _ line chart

PaulOlding — Wed, 22 Dec 2021 15:22:13 GMT

Hi Anonymous

Calculated columns aren't dynamic in Power BI. That is, slicer selections do not affect the values in them as they are not recalculated.

Measures are dynamic, so that's what to use.

Here's a measure to get the values. You may want to think about what value should be displayed at the total level. That isn't specified in your question

incident per period value = VAR _MileagePerPeriod = ADDCOLUMNS( SUMMARIZE( Incidents, Incidents[Period], Incidents[ Mileage per period] ), "@Rows", CALCULATE(COUNTROWS(Incidents)) ) VAR _Result = SUMX(_MileagePerPeriod, DIVIDE(Incidents[ Mileage per period], [@Rows]) ) RETURN _Result