cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Filtering unpivoted data in a measure

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    
DateTimeDateTimeTagValue
11/24/20 1:0011/24/20201:00IsRunning1
11/24/20 2:0011/24/20202:00IsRunning1
11/24/20 3:0011/24/20203:00IsRunning1
11/24/20 4:0011/24/20204:00IsRunning0
11/24/20 5:0011/24/20205:00IsRunning0
11/24/20 6:0011/24/20206:00IsRunning0
11/24/20 7:0011/24/20207:00IsRunning0
11/24/20 8:0011/24/20208:00IsRunning1
11/24/20 9:0011/24/20209:00IsRunning1
11/24/20 10:0011/24/202010:00IsRunning1
11/24/20 1:0011/24/20201:00Pressure13.4
11/24/20 2:0011/24/20202:00Pressure13.3
11/24/20 3:0011/24/20203:00Pressure13.2
11/24/20 4:0011/24/20204:00Pressure0.2
11/24/20 5:0011/24/20205:00Pressure0.2
11/24/20 6:0011/24/20206:00Pressure0.1
11/24/20 7:0011/24/20207:00Pressure0.2
11/24/20 8:0011/24/20208:00Pressure13.1
11/24/20 9:0011/24/20209:00Pressure13.3
11/24/20 10:0011/24/202010:00Pressure13.2

 

1 ACCEPTED SOLUTION
Super User III
Super User III

@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 

SU18_powerbi_badge

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

@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 

SU18_powerbi_badge

 

View solution in original post

Super User III
Super User III

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 

SU18_powerbi_badge

 

Frequent Visitor

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors