SUMIF and VLOOKUP within BI

Question

Hi,&nbsp;I'm new to power Bi and trying to manipulate some data from my SQL database.&nbsp;This is a screenshot of the data tables im trying to work on:&nbsp;&nbsp;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&nbsp;I can then manipulate this to give information on the other Channels.&nbsp;I'm not sure the best way to approcah this, do i need to create columns or is it a measure function?&nbsp;Hoping someone can help me,&nbsp;Thanks

eric_zhang · Answer

chrisread9907
You can try this as well.
&nbsp;
Measure = 
CALCULATE (
    SUM ( YourTable[DurationSeconds] ),
    FILTER (
        YourTable,
        YourTable[slaveId] = 1
            &amp;&amp; INT(YourTable[dateEnd]) = INT(TODAY ())
            &amp;&amp; YourTable[channel1] = 1
            &amp;&amp; YourTable[error] = FALSE ()
    )
)

greg_deckler · Answer

Create a measure like:&nbsp;SumDurationInSeconds = SUM([DurationInSeconds])Then, create another measure like this:&nbsp;ConditionalSumDiS =&nbsp;CALCULATE(SumDurationInSeconds, [channel] = "1" &amp; [error] = "False" &amp; [slaveid] = "1" &amp; [dateEnd] = TODAY())Something like that.

chrisread9907 · Answer

Thanks very much,&nbsp;I have tried to add both measures but get this messgae:Have i done something wrong?&nbsp;How do i use a measure with graphical reports? can i just drag it out to create a graph etc?&nbsp;Thanks,

greg_deckler · Answer

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.

greg_deckler · Answer

Let's go a different route, it's a little ugly but should accomplish what you want.&nbsp;Create a new calculated column:&nbsp;MyDurationInSeconds = IF([channel] = "1",IF([error] = "False",IF([slaveid] = "1" ,IF([dateEnd] = TODAY(),[DurationInSeconds],0),0),0),0)Then, you can create a measure to just sum "MyDurationInSeconds".

Forum Discussion

8 Replies

Related content