Forum Discussion

BachFel's avatar
BachFel
Icon for Helper II rankHelper II
8 years ago
Solved

Calculate, sum? need help with a formula

Following problem: in the table LN[Leistungsbeschreibung] can be more than one row with the text "Objektbegehung", per ID. i.E.:   ID          Leistungsbeschreibung       Datum  111        Objek...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    I used ID instead of KostElementID, might be a little confusing because you have several IDs

     

    FILTER(LN;LN[KostElementID]=EARLIER(KontElement[KostElementID]) hopefully works now.

BachFel's avatar
BachFel
Icon for Helper II rankHelper II
8 years ago
Floriankx wrote:

Hello I don't get your point exactly.

 

Why is in your example the 222 Blank?

 

If you want to do it with DAX try the following:

CALCULATE(Min(LN[Datum]);FILTER(LN;LN[Leistungsbeschreibung)="Objektbegehung")))

 

So you get the earliest for each ID where Leistungsbeschreibung = Objektbehung.

 

I hope I understood you correctly.

 

Hello, the ID 222 should be blank because the text is not "Objektbegehung".

When I use your DAX formula, the output is that for every ID there is the same date (it is the earliest date with the text "Objektbegehung".

By the way: the formula also needs a filter for table KontElement [mandatID]=1.

 

 

Hope you can help

Floriankx's avatar
Floriankx
Icon for Solution Sage rankSolution Sage
8 years ago

Hello,

 

you're right. Formula was incomplete:

 

=CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung");FILTER(LN;LN[ID]=EARLIER(KontElement[ID])))

 

Mit freundlichen Grüßen

Best regards

  • BachFel's avatar
    BachFel
    Icon for Helper II rankHelper II
    8 years ago
    Floriankx wrote:

    Hello,

     

    you're right. Formula was incomplete:

     

    =CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung");FILTER(LN;LN[ID]=EARLIER(KontElement[ID])))

     

    Mit freundlichen Grüßen

    Best regards

     

    Hello,

     

    I didn´t get it.

    Until =CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung"); everthing is clear to me.

    Can u please forget the second filter (mandatID) i mentioned before and built the formula with the new expression earlier?

     

    in hope to understand it and thank u a lot

    • Floriankx's avatar
      Floriankx
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Hello,

       

      FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

      Earlier works in row context. It takes the ID of the current row in LN and compares it to the IDs of KontElement and so filters all the IDs which are similar to the current ID (also order of the formular suggests upside down). The second filter eliminates all except Objektbegehung and the MIN Term gives you the earliest date.

      • BachFel's avatar
        BachFel
        Icon for Helper II rankHelper II
        8 years ago

         

         

        Probably I still didn´t get it..