Hello,
I have a table that contains the parameters of machines and the dates when the parameters were checked.
Parameter names are in the “Parameter” column, parameter values – “Value”, dates – “Date”.
I have to take two time periods (Base and Test) and compare them. I need to compare their distributions visually by using a histogram/ violin plot/ box plot/ or any other graph that shows the distribution of the data. In addition to that, unless it is provided by the graph, I have to calculate statistics like min, max, mean, avg, std, and some custom stats basing on the parameters.
The report is going to be published to the local PBI server, so the end-user won’t be able to change query parameters.
I have tried two solutions, but they didn’t work for me.
1) First solution
I create two date slicers and measurements that take the first and the last date of the interval.
Example of the measurement that takes the last interval:
SelectedDateEndBaseline =
RETURN CALCULATE (
MAX('MainTable'[Date]),
ALLSELECTED ()
)
For the start interval I do the same, but I take MIN('MainTable'[Date]).
In the end I have 4 measurements that contain my dates:
- SelectedDateStartBaseline
- SelectedDateEndBaseline
- SelectedDateStartTest
- SelectedDateEndTest
Next, I create a calculated column that returns “Base Condition” or “Test Condition” basing on the time interval:
ConditionColumn = IF(
AND(
[Date]>=[SelectedDateStartBaseline],
[Date]<=[SelectedDateEndBaseline]),”Base Condition”
IF(
AND(
[Date]>=[SelectedDateStartTest],
[Date]<=[SelectedDateEndTest]),"Test Condition"))
After that, I create a violin plot and use “ConditionColumn” in the “Category” field. The problem is that the calculated column is not being updated when I change dates in the slicers.
2) Second solution
I create a separate “Date” table for the Base interval:
Date =
VAR MinYear = YEAR ( MIN ( MainTable[Date] ) )
VAR MaxYear = YEAR ( MAX ( MainTable[Date]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "dddd" ),
"Weekday number", WEEKDAY( [Date] ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)
I create another table that contains only the “Date” column for Test Interval:
Test Date = ALLNOBLANKROW( 'Date' )
Next, I create two measurements that filter Baseline rows and Test rows separately with different slicers.
Baseline Values = SUMX( Value, MainTable[Value] )
Test Values = CALCULATE(
[Baseline Values],
ALL ( 'Date' ),
USERELATIONSHIP( 'Date'[Date], 'Test Date’ [Date] )
)
Now I have two measurements that change their values when I change the corresponding slicers.
I can calculate stats separately by creating more measures like:
Baseline Values Avg = AVERAGEX( Value, MainTable[Value] )
Baseline Values MIN = MINX( Value, MainTable[Value] ),
Etc…
The problem with this method is that I cannot plot these two measures on the same graph (violin, box, or histogram). I thought of creating two separate violin plots and hardcoding y-axis END and START values, but I have a slicer for the “Parameter” column; consequently, when I change the parameter, the range of values are different.
How should I proceed with the problem?
Thanks.
