Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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()
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |