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.
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.
Thanks
Rohan
Solved! Go to 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.
Hi @Anonymous
Are the values calculated in four lines from the same table?
Can i use the data below to test?
Best Regards
Maggie
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
Sample Screenshot
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.
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:
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.
Hi @v-juanli-msft ,
Just a quick followup on this. Can you please upload the .PBIX file as well.
Thanks
Rohan
Hi @Anonymous ,
can you provide a sample PBIX?
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |