Filter to populate one line on the graph

[Anonymous]

Hi

Can someone please help

I wanted to test 2 conditions 

lets say we have a line graph, when condition 1 period & personnel is selected, it will populate one line on the graph, when condition 2 is selected, it will populated another line on the graph. So we can compare the value column in these 2 conditions.

so we would want to duplicate the filters to have 2 sets of filter ( condition 1 ( period and personnel ) and condition 2 period and personnel)

thanks

Condition Period	Condition Personnel	Value
Jan-21	AA	175
Feb-21	BB	376
Mar-21	CC	235
Apr-21	DD	274
May-21	EE	471
Jun-21	FF	154
Jul-21	GG	159
Aug-21	KK	229
Sep-21	LL	285
Oct-21	MM	441
Nov-21	NN	265
Dec-21	OO	404
Jan-22	PP	387
Feb-22	QQ	316
Mar-22	RR	413
Apr-22	SS	139
May-22	TT	440
Jun-22	UU	306
Jul-22	VV	249
Aug-22	WW	416
Sep-22	XX	424
Oct-22	YY	140
Nov-22	ZZ	413
Dec-22	AS	230
Jan-23	AF	429
Feb-23	ER	138
Mar-23	FS	485
Apr-23	TE	266
May-23	JF	330
Jun-23	DJ	426
Jul-23	DT	341
Aug-23	WB	260
Sep-23	CJ	127
Oct-23	VY	405
Nov-23	FJ	443

[MFelix]

Hi  @Anonymous ,

What are the conditions you want to test? Without knowing the conditions is difficult to give you an answer, bvut this passes for making use of two measures and a slicer look at the video below with a similar explanation for tuining on and off measures on a chart.

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Turn-Measures-On-and-Off-Inside-a-Chart-with-CONTAINS/td-p/151045

[Anonymous]

Hi 

By conditions I mean ( any values within the column Period and Personnel) .

So we should have 2 groups of filter,

first group ( period and personnel) , second group ( period and personnel again)

so a user can select any value from Period and Personnel in the first group as scenior 1 and then select another value for period and personnel (second group) at the same time to compare

[MFelix]

Hi @Anonymous ,

You need to create to tables for the slicers then just add the following measures:

Slicer 1 Calculation =
CALCULATE (
    SUM ( 'Table'[Value] );
    FILTER (
        'Table';
        'Table'[Condition Period] IN VALUES ( 'Slicer 1'[Condition Period] )
            && 'Table'[Condition Personnel] IN VALUES ( 'Slicer 1'[Condition Personnel] )
    )
)

Slicer 2 Calculation =
CALCULATE (
    SUM ( 'Table'[Value] );
    FILTER (
        'Table';
        'Table'[Condition Period] IN VALUES ( 'Slicer 2'[Condition Period] )
            && 'Table'[Condition Personnel] IN VALUES ( 'Slicer 2'[Condition Personnel] )
    )
)

Now just add the slicers and the measure to your chart.

Check PBIX file attach.

[Anonymous]

Hi sorry one more question - by default, the 2 calcuations should both equal to 10991 since no filter has been selected.

But if you look at the graph, the total for slicer 2 calcuation is incorrect.. can you please help ,cheers - i have exported the table for your reference

Condition Period		Slicer 1 Calculation	Slicer 2 Calculation
01/01/2021 00:00		175
01/02/2021 00:00		376
01/03/2021 00:00		235
01/04/2021 00:00		274
01/05/2021 00:00		471
01/06/2021 00:00		154
01/07/2021 00:00		159
01/08/2021 00:00		229
01/09/2021 00:00		285
01/10/2021 00:00		441
01/11/2021 00:00		265	265
01/12/2021 00:00		404
01/01/2022 00:00		387
01/02/2022 00:00		316
01/03/2022 00:00		413
01/04/2022 00:00		139
01/05/2022 00:00		440
01/06/2022 00:00		306
01/07/2022 00:00		249
01/08/2022 00:00		416
01/09/2022 00:00		424
01/10/2022 00:00		140
01/11/2022 00:00		413
01/12/2022 00:00		230
01/01/2023 00:00		429
01/02/2023 00:00		138
01/03/2023 00:00		485
01/04/2023 00:00		266
01/05/2023 00:00		330	330
01/06/2023 00:00		426
01/07/2023 00:00		341
01/08/2023 00:00		260
01/09/2023 00:00		127
01/10/2023 00:00		405
01/11/2023 00:00		443

[MFelix]

Hi @Anonymous ,

Based on my file I have reset the filters and everything is correct.

I have append a new file with 3 cards one for values on original table and the other two with the slicer calculated values as you can see 3 cards are the same and the lines on the line charts are equal also, so everything working properly.

Did you clear all the filter from your slicer 2? Are you sure the tables from slicer 2 are exactly equal to slicer 1 tables and to Values table?

From the table you present the slicer for personnell in slicer 2 as only 2 values selected. you need to reset it also

[Anonymous]

Thank you so much ! all sorted ! have a nice evening

[Anonymous]

Thank you ! you are a star

[MFelix]

Hi @Anonymous ,

No problem with that you can do it, if you are making that kind of setup with 4 tables for slicers I advise you to make the dates tables continuous so going from 1 January till 31 december. It will help you in the future if you want to make YTD, MTD or any other time inteliggence calculations.

[Anonymous]

thank you! its a really good suggestion.