Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chrisread9907
Frequent Visitor

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:

 

Capture.PNG

 

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 8
Eric_Zhang
Employee
Employee

@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 ()
    )
)

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

 

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

@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
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks very much,

 

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

Capture.PNG

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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".


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.