Plot Line Chart from 2 independent date slicer

[Anonymous]

Hello Power BI community,

Hope everyone is safe and doing good.

I need help in regards to a chart layout requirement .

I have 2 date slicers
Slicer_Date1 and Slicer_Date2

I need to show a line chart wherein it would have 4 lines such that Line 1, Line2,Line 3 depends on Slicer_Date1 whereas Line 4 depends on Slicer_Date2.

The X-Axis contains the date
and Y-Axis the KPI's :
Line 1 --> Upper Limit ( between date range selection in Slicer_Date1)
Line 2 --> Average ( between date range selection in Slicer_Date1)
Line 3 --> Lower Limit ( between date range selection in Slicer_Date1)

Line 4 --> Total Count ( between date range selection in Slicer_Date2)

As Line 4 is totally independant of date selected in Slicer_Date1 hence I am not able to figure out a way to show it in the same chart as the X axis dates may vary.

I would appreciate if someone can assist me on this requirement.

Sample Layout

Thanks

Rohan

[v-juanli-msft]

Hi @Anonymous 

Are the values calculated in four lines from the same table?

Can i use the data below to test?

Best Regards
Maggie

[Anonymous]

Hi @v-juanli-msft ,

Apologies for the delayed response. Yes you can  use a similiar data.

The 2 date slicers are from the same table/source but are independant of each other i.e.  I have created a copy of the table and sourcing from each of the 2 tables.

Unfortunately I cannot upload a sample file as I don't see an option to do so as well as I can't upload it to a dropbox or One drive Hence attaching a screenshot.

Goal is to club Chart 1 and Chart 2 into 1 single chart provided Value 1 changes wrt to Slicer1 and Value 2 changes wrt Slicer 2 only.

Hope it helps.

Thanks Again

Rohan

[Anonymous]

Sample Screenshot

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Two new date tables and measures created as below:

min =
CALCULATE (
    MIN ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


max =
CALCULATE (
    MAX ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


average =
CALCULATE (
    AVERAGE ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


countall =
COUNT ( 'Table 2'[id] )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey @v-juanli-msft ,

How would you implement a Lower Control Limit instead of MIN for these formulas? I have tried to change this min to a measure I have which is basically doing this:

Lower Control Limit formula is AVERAGE(VALUE) - STD.DEV(VALUE)*3

I tried to create the formula replacing minimum but unable to get it to work. It is giving me same value as average.

[Anonymous]

Thanks @v-juanli-msft  for the updates.

Can you forward the .PBIX file so that I can give it a shot at my end. Need to validate the Date slicer mapping with mine.

[Anonymous]

Hi @v-juanli-msft ,

Just a quick followup on this. Can you please upload the .PBIX file as well.

Thanks

Rohan 

[mwegener]

Hi @Anonymous ,

can you provide a sample PBIX?