Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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 () ) )
This is spot on, give the figure i need, Thanks.
Can I create another measure to perfom a calculation on this mease?
@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?
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.
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,
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.
Let's go a different route, it's a little ugly but should accomplish what you want.
Create a new calculated column:
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".
Also, remember that you could just sum "DurationInSeconds", put it in a card visualization and then add the other IF conditions as filter values or as slicers and do your filtering that way. That's a lot better if you have multiple different kinds of scenarios that you want to get that value for.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |