Forum Discussion

SachinC's avatar
SachinC
Helper V
8 years ago
Solved

Advanced Filtering and Custom Formulae

Hi, I have some project data in Excel that I report on using PowerBI. Here's a snippet of the data and some information on what I wish to do:-   Requirement: Duration Days excluding 'Pushout'. Fo...
Need Help
  • SachinC's avatar
    SachinC
    8 years ago

    I worked in the end. Rubbish PowerBI - the visual I had didn't work, so I created a new one and it worked.  Please fix this in the next release of PowerBi as it was driving me insane! Thanks for your help.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi SachinC,

 

Try this formula please. I didn't find out what kind of characters ahead some of the "Task Name". So I used a function "Right".

Measure =
VAR allDuration =
    CALCULATE ( SUM ( Table1[Duration.1] ), 'Table1'[#] = 1 )
VAR Pushout =
    CALCULATE (
        SUM ( Table1[Duration.1] ),
        RIGHT ( 'Table1'[Task Name], 7 ) = "Pushout"
    )
RETURN
    allDuration - Pushout

The Duration isn't numbers. We need to transfer it first. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • SachinC's avatar
    SachinC
    Helper V
    8 years ago

    Hi Dale,

    Good suggestions - however it's not doing the trick. I need to also negate #=2 as this is a repeat for #=1 - does that make sense?

    I look forward to your response.

    Regards,

    S

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi SachinC,

       

      I kick the #=2 out in the formula directly. So we don't need to care #=2. What's your idea? You can try one and see the result.

       

      Best Regards!

      Dale

      • SachinC's avatar
        SachinC
        Helper V
        8 years ago

        Yes _ i know you strip out #=2, however it's not working for me still. Let me have another try again, but thanks for your help.

        I'm just wondering what checking routine, via formulae, I can plug in to test? Can you help me with this?
        Thank you.