Filtered Matrix with slicer see the difference instead of total

[Newbie45]

Hi, i have a big source table from an ocdb-database who looks something like this:

I would like to visualize the difference between the value at 12AM and 11PM on the same day.

So instead of the automatic grand total I would like to see the grand difference if this is possible.

The date for the matrix is selectable via a slicer and the matrix is filtered in ID_Name and time.

I only want the viewer to be able to change the date but not ID or time.

Should look like this:

PS: The date has the format Day.Month.Year

For help I would be very grateful because I am not yet so good at power bi!

[v-yueyunzh-msft]

Hi, @Newbie45 

Thanks for your sample data first!

Here are the steps you can refer to :
(1)This is my test data:

(2)If you want to output the data in the format you have given, then we can reconstruct the dimension table in Power Query, we can create a blank query and put this in the advanced Editor:

let
    Source = Table.FromList(List.Distinct(Table[ID_Name]), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {"12:00 AM","11:00 PM"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type" = Table.InsertRows(Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type time}}),2,{}),
    test = Table.RenameColumns(#"Changed Type",{{"Column1", "ID_Name"}, {"Custom", "Time"}}),
    Custom1 = List.Accumulate( List.Generate(()=>2,each _<=Table.RowCount(test)+Table.RowCount(test)/2,each _+3),test,(x,y)=>Table.InsertRows(x,y,{
[ID_Name="Difference",Time=""]
})),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then  we can get this table like this:

(3)Then we can apply the data to desktop and we do not need to create any relationship between tables.

Then we need to create four measures:

Value Measure = var _ID_Name = MAX('Query1'[ID_Name])
var _time= MAX('Query1'[Time])
var _value =SUMX( FILTER('Table','Table'[ID_Name]=_ID_Name && 'Table'[Time]= _time) , [Value])
return
_value

Value Measure 2 = var _cur_index = MAX('Query1'[Index])
var _table=  ADDCOLUMNS(  ALLSELECTED('Query1') , "value measure" , [Value Measure])
var _index_minus2 = SUMX( FILTER(_table, [Index] =_cur_index-2) , [value measure])
var _index_minus1 = SUMX( FILTER(_table, [Index] =_cur_index-1) , [value measure])
var _cur_IDNAME= MAX('Query1'[ID_Name])
 return
IF(_cur_IDNAME= "Difference" ,  _index_minus2 - _index_minus1 , [Value Measure])

Unit Measure = var _ID_Name = MAX('Query1'[ID_Name])
var _time= MAX('Query1'[Time])
var _value =MAXX( FILTER('Table','Table'[ID_Name]=_ID_Name && 'Table'[Time]= _time) , [Unit])
return
_value

Unit Measure 2 = var _cur_index = MAX('Query1'[Index])
var _table=  ADDCOLUMNS(  ALLSELECTED('Query1') , "unit measure" , [Unit Measure])
var _index_minus1 = MAXX( FILTER(_table, [Index] =_cur_index-1) , [Unit measure])
var _cur_IDNAME= MAX('Query1'[ID_Name])
 return
IF(_cur_IDNAME= "Difference" , _index_minus1 , [Unit Measure])

We can out the fields on the visual and we can get this in the Matrxi visual:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Newbie45]

Hi Aniya Zhang,

thank you very much for your help.

Unfortunately, an error occurs within my query.

The Error looks like this:

= let

    tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),

    recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),

    fieldNames = Record.FieldNames(recordTypeFields),

    fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),

    pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})

in

    Table.TransformColumns(Source, pairs)

I guess something with my types is off...

In the table i have changed the types of the columns of the original odbc-file from text to date and time.

Orginal file:  Date = 20230501 (text)     Time = 010000 (text)

Table:            Date = 01.05.2023 (Date)   Time = 01:00:00 (time)

Could this be a problem?

Best Regards,

[v-yueyunzh-msft]

Hi, @Newbie45 

Thanks for your quick response!

Based on the information you provided, this appears to be your M code. It's hard to see where the problem is, you can try to check which step you have the problem with, or you can delete your sensitive data, then publish your pbix to OneDrive, and then share it with me as a link.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly