Filtering unpivoted data in a measure

[pagrosse]

Hey guys, I'm trying to figure out a filter or conditional statement that will only return pressure values when IsRunning=1.  I know how to perform this in a pivoted context but unsure with the unpivoted data structure of PowerBI.  I could just pivot the data but from what I've read, Power BI is more efficient with data in the unpivoted structure.

Current measure that returns all values (when IsRunning = 0 or 1):

Pressure = Calculate(average('Query1'[Value]), 'Query1'[Tag]="Pressure")

Thanks!

Query1
DateTime	Date	Time	Tag	Value
11/24/20 1:00	11/24/2020	1:00	IsRunning	1
11/24/20 2:00	11/24/2020	2:00	IsRunning	1
11/24/20 3:00	11/24/2020	3:00	IsRunning	1
11/24/20 4:00	11/24/2020	4:00	IsRunning	0
11/24/20 5:00	11/24/2020	5:00	IsRunning	0
11/24/20 6:00	11/24/2020	6:00	IsRunning	0
11/24/20 7:00	11/24/2020	7:00	IsRunning	0
11/24/20 8:00	11/24/2020	8:00	IsRunning	1
11/24/20 9:00	11/24/2020	9:00	IsRunning	1
11/24/20 10:00	11/24/2020	10:00	IsRunning	1
11/24/20 1:00	11/24/2020	1:00	Pressure	13.4
11/24/20 2:00	11/24/2020	2:00	Pressure	13.3
11/24/20 3:00	11/24/2020	3:00	Pressure	13.2
11/24/20 4:00	11/24/2020	4:00	Pressure	0.2
11/24/20 5:00	11/24/2020	5:00	Pressure	0.2
11/24/20 6:00	11/24/2020	6:00	Pressure	0.1
11/24/20 7:00	11/24/2020	7:00	Pressure	0.2
11/24/20 8:00	11/24/2020	8:00	Pressure	13.1
11/24/20 9:00	11/24/2020	9:00	Pressure	13.3
11/24/20 10:00	11/24/2020	10:00	Pressure	13.2

[AlB · Accepted solution]

@pagrosse 

 Pivoting the Tag columns would make things much easier. If you do this in PQ:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdLNCsIwDMDxV5Geh0vS7ss38CZex45DvPSwsveXKcjiYpJjAz8KyX8cA2JNqSY44QUgVN83vR+f2bXc15yf+bGNwlTtFAmKTBUFFU2VBJWOCrhqBNWYqhVUa6pOUJ2pekH15jYGQQ2mQpDODLb7X8dtmUtZl3mbxHNy98FddBfCHbkb2Tn4ZUokGlMqYQzdmWi/KZ3wnfhLUW+gtnK4wvQC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, Date = _t, Time = _t, Tag = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Date", type date}, {"Time", type time}, {"Tag", type text}, {"Value", type number}}),

    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Tag]), "Tag", "Value", List.Sum)
in
    #"Pivoted Column"

it woud be as simple as:

Measure_pivoted =
CALCULATE ( AVERAGE ( 'Table2'[Pressure] ), 'Table2'[IsRunning] = 1 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @pagrosse 

It's certainly an unusual way of doing it. PRocessing the data with PQ first would be better to get it in a more manageable format. You can do:

Measure = 
AVERAGEX (
    CALCULATETABLE (
        DISTINCT ( Table1[DateTime] ),
        Table1[Tag] = "IsRunning",
        Table1[Value] = 1
    ),
    CALCULATE ( SUM ( Table1[Value] ), Table1[Tag] = "Pressure" )
)

or

Measure2 = 
CALCULATE (
    AVERAGE ( Table1[Value] ),
    Table1[Tag] = "Pressure",
    CALCULATETABLE (
        DISTINCT ( Table1[DateTime] ),
        Table1[Tag] = "IsRunning",
        Table1[Value] = 1
    )
)

or

Measure3 =
AVERAGEX (
    FILTER (
        Table1,
        Table1[Tag] = "Pressure"
            && CALCULATE (
                SUM ( Table1[Value] ),
                Table1[Tag] = "IsRunning",
                ALLEXCEPT ( Table1, Table1[DateTime] )
            )
    ),
    Table1[Value]
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[pagrosse]

Awesome! Thanks! I give these a try.  You mention that this is an unusual way.   Should I pivot all of this data so its easier to manipulate?  This would make my life easier but I've read that you want all your data in unpivoted form since PowerBI is optimized to handle this type of data structure