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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BachFel
Helper II
Helper II

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        Objektbegehung                 20.03.2018

222        abcd                                    14.01.2018

111        Objektbegehung                 01.01.2018

 

I want to create a formula in another table KontElement[Datum]  (where all ID´s are listed once). Output should be:

 

ID      Datum 

111    01.01.2018

222    Blank

 

Only the earliest date (per ID) should be listed. If the text is not "Leistungsbeschreibung" ist should be Blank()

1 ACCEPTED SOLUTION

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.

View solution in original post

10 REPLIES 10
Floriankx
Solution Sage
Solution Sage

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.


@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

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


@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

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.

PBI.JPG

 

Unbenannt.JPG

 

Probably I still didn´t get it..

Hello,

 

you wrote:

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

instead of

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

 

Please correct and let me know

Hello,

 

I wrote


 

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

instead of

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

 

 

because in the table LN there is now column with the expression you took.

 


 

Hi @BachFel,

 

Please no noticed that generally EARLIER() function is used in a calculated column, not a measure.

 

Thanks,
Xi Jin.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.