Flag first text occurrence according to date for further calculation

[flex99]

Hi,

In a chronological table sorted by date, I need to find the first occurrence of a text for each day. Each text belongs to a value which shall not be used more than once for a calculation. The target is to flag the first instance to then use its respective value for multiplication by a corresponding factor (see below table).

What I tried so far was the following (flag the first occurrence by 1/0).

FirstOccurrence =

IF (

    CALCULATE (

        COUNTROWS ( table1 );

        FILTER (

            ALLEXCEPT ( table1; table1[text] );

            table1[date] <= EARLIER ( table1[date] )

        )

    )

        > 1;

    0;

    1

)

However, this didn’t work out. Even if, I would then face the problem to do the calculation for the final result, but I guess this would be only a simple if/then column.

The result is expected to look like this:

Date	Text	Value	Factor	First Occurrence	Result
03.02.2020	Text 1	10	1	1	10
03.02.2020	Text 2	20	2	1	40
03.02.2020	Text 3	30	3	1	90
03.02.2020	Text 1	15	4	0	0
03.02.2020	Text 5	25	5	1	125
03.02.2020	Text 6	35	6	1	210
03.02.2020	Text 7	50	7	1	350
03.02.2020	Text 8	55	8	1	440
03.02.2020	Text 2	60	9	0	0

I’d highly appreciate any help since I’m not that advanced in Power BI coding.

[flex99]

Nobody any idea?

I shortened it down to the following issue as I would only need to know how to enable the following within Power BI to build up on that solution:

If for the same date in a given table "Text" contains 'Text1' and "FactorText" contains 'FactorText1' then multiply 'Value' (of Text) with 'Factor' (of FactorText).

However, 'Value' and 'Factor' will in most cases not be in the same row which is why I need to conncet the query to the date!

Date	Text	Value	FactorText	Factor	SingleResult
03.02.2020	Text 1	10	FactorText1	1	10

Could anyone help me to achieve that somehow?

Thanks for your support in advance!

[flex99]

Sorry, I played with that reference to fix my issue, but I cannot apply the reference to my example.

Can someone help? I need something like that:

If 'Text1' in "Text" Then

"Value of Text1" x "Factor of respective "Factor1" (Factor might be located in a different row but has the same date and occurs only once)

Quite hard for an amateur to start a project 😞

When this is achieved, I could count the occurrance of "Text1" and divide the above result by the number of counts.

[flex99]

Sorry, but I made a mistake when setting up the sample table. Actually, “Factor” will be the same for each specific “Text” but is only listed once for each date (see below).

Date	Text	Value	FactorText	Factor	SingleResult
03.02.2020	Text 1	10	FactorText1	1	10
03.02.2020	Text 2	20	FactorText2	2	40
03.02.2020	Text 3	30	FactorText3	3	90
03.02.2020	Text 1	10			10
03.02.2020	Text 2	20			40
03.02.2020	Text 2	20			40

Therefore, I came up with a work around idea for which I don’t need the first occurrence:

The idea is for each date (!) to multiple the values of column “Text” and its respective “Factor”. But I don’t know how the code needs to look like to give the “SingleResult” as I need to perform this for each date.

The second thing would then be to count how often i.e. ‘Text1’ exists for a distinct date and simply divide the sum of above “SingleResults” for each “Text” by this number! But I’m sorry to admit that I would also need some help on that one.

Text	FinalResult
Text 1	10 (2*10/2)
Text 2	40 (3*40/3)
Text 3	90 (1*90)

Thanks for the support you gave so far – greatly appreciated!

P.S.: Unfortunately, the date does not have a time stamp.

[amitchandak]

I think you should try allexcpet , that you might need at date, text level 

refer 

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

You can try sum of Max

[amitchandak]

Does this date has time stamp?  the you can create a measure like

countx(filter(table1, table1[date] <= EARLIER ( table1[date] )),table1[date])

else something else is needed to decide the first event.

[HotChilli]

A column will be needed to sort the data apart from Date.  Can we rely on Factor or Value to define order (if day is the same)?