Forum Discussion

chrisread9907's avatar
chrisread9907
Frequent Visitor
10 years ago

SUMIF and VLOOKUP within BI

Hi,

 

I'm new to power Bi and trying to manipulate some data from my SQL database.

 

This is a screenshot of the data tables im trying to work on:

 

 

What i am looking to do is do a SUM of Column 5 (DurationinSeconds) IF Column 5 (channel) = "1" IF Column 10 (error) = "False" IF Column 2 (slaveId) = "1" and if the (datEnd) = Today

 

I can then manipulate this to give information on the other Channels.

 

I'm not sure the best way to approcah this, do i need to create columns or is it a measure function?

 

Hoping someone can help me,

 

Thanks

8 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    chrisread9907

    You can try this as well.

     

    Measure = 
CALCULATE (
    SUM ( YourTable[DurationSeconds] ),
    FILTER (
        YourTable,
        YourTable[slaveId] = 1
            && INT(YourTable[dateEnd]) = INT(TODAY ())
            && YourTable[channel1] = 1
            && YourTable[error] = FALSE ()
    )
)
    • chrisread9907's avatar
      chrisread9907
      Frequent Visitor
      10 years ago

      This is spot on, give the figure i need, Thanks.

       

      Can I create another measure to perfom a calculation on this mease?

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        chrisread9907

        chrisread9907 wrote:

        This is spot on, give the figure i need, Thanks.

         

        Can I create another measure to perfom a calculation on this mease?

         

        Surely you can. What is the problem when use it in anothere measure?

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Create a measure like:

     

    SumDurationInSeconds = SUM([DurationInSeconds])

    Then, create another measure like this:

     

    ConditionalSumDiS = CALCULATE(SumDurationInSeconds, [channel] = "1" & [error] = "False" & [slaveid] = "1" & [dateEnd] = TODAY())

    Something like that.

    • chrisread9907's avatar
      chrisread9907
      Frequent Visitor
      10 years ago

      Thanks very much,

       

      I have tried to add both measures but get this messgae:

      Have i done something wrong?

       

      How do i use a measure with graphical reports? can i just drag it out to create a graph etc?

       

      Thanks,

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Sorry, my bad, you need to add a aggregation function when referencing columns in a measure (SUM, AVERAGE, etc.) Let me review once again what you are trying to accomplish and see if you can achieve that via this method or if we need to go another route. That's what I get for trying to multitask.