Writing Values in counted rows

[bagabo]

Hi!

So im a little unsafe about getting my code to work. As you can see in the image, the first step is to calculate, depending on a specific date, in this case the 12.06.2019 with the NOTI Type "MS", how many rows with NOTI Type "MI", the same SYS Serial Number and wihtin 28 days are available. So right here ist calculated, correctly, the number 4.  This happens with this code:

Next 28 Days = 
VAR _CurrSerial = MS_MI_Meldungen[SYS Serial Number]
VAR _CurrDate = MS_MI_Meldungen[NOTI Created On]
VAR _NextDate = DATEADD(MS_MI_Meldungen[NOTI Created On];1;MONTH)

RETURN

    IF(MS_MI_Meldungen[NOTI Type] = "MI";
    COUNTROWS(FILTER(
        ALL(MS_MI_Meldungen);
        MS_MI_Meldungen[SYS Serial Number] = _CurrSerial &&
        MS_MI_Meldungen[NOTI Created On] >= _CurrDate &&
        MS_MI_Meldungen[NOTI Created On] <= _NextDate &&
        MS_MI_Meldungen[NOTI Type] = "MS"
        
    )))

For the next step I'd like to mark every row that was in this calculation (in another Column), so I can filter for these. In this example, all the 4 rows beyond the first one, wich is circled red, should have a 1 or something. 
How can I do this?

Thanks in advance!

[v-rzhou-msft]

Hi @bagabo 

Please use Earlier function in your dax code to locate the current row if you want to build calculated columns.

Try calculated columns as below.

Count of MS Per MI = 
VAR _Count =
    CALCULATE (
        COUNTROWS ( MS_MI_Meldungen ),
        FILTER (
            MS_MI_Meldungen,
            MS_MI_Meldungen[NOTI Type] = "MS"
                && MS_MI_Meldungen[SYS Serial Number]
                    = EARLIER ( MS_MI_Meldungen[SYS Serial Number] )
                && MS_MI_Meldungen[NOTI Created On] >= EARLIER ( MS_MI_Meldungen[NOTI Created On] )
                && MS_MI_Meldungen[NOTI Created On]
                    <= EARLIER ( MS_MI_Meldungen[NOTI Created On] ) + 28
        )
    )
VAR _Result =
    IF ( MS_MI_Meldungen[NOTI Type] = "MI", _Count )
RETURN
    _Result

Group = 
VAR _T =
    ADDCOLUMNS (
        MS_MI_Meldungen,
        "RankMI",
            RANKX (
                FILTER ( MS_MI_Meldungen, MS_MI_Meldungen[NOTI Type] = "MI" ),
                MS_MI_Meldungen[NOTI Created On],
                ,
                ASC
            )
    )
VAR _LastMISYS =
    MAXX (
        FILTER (
            _T,
            MS_MI_Meldungen[NOTI Created On] < EARLIER ( MS_MI_Meldungen[NOTI Created On] )
                && MS_MI_Meldungen[NOTI Type] = "MI"
                && MS_MI_Meldungen[SYS Serial Number]
        ),
        [SYS Serial Number]
    )
VAR _LastCreatedtime =
    MAXX (
        FILTER (
            _T,
            MS_MI_Meldungen[NOTI Created On] < EARLIER ( MS_MI_Meldungen[NOTI Created On] )
                && MS_MI_Meldungen[NOTI Type] = "MI"
                && MS_MI_Meldungen[SYS Serial Number]
        ),
        [NOTI Created On]
    )
VAR _GroupMS =
    MAXX (
        FILTER (
            _T,
            MS_MI_Meldungen[NOTI Created On] < EARLIER ( MS_MI_Meldungen[NOTI Created On] )
                && MS_MI_Meldungen[NOTI Type] = "MI"
        ),
        [RankMI]
    )
VAR _GroupMI =
    MAXX (
        FILTER (
            _T,
            MS_MI_Meldungen[NOTI Created On] = EARLIER ( MS_MI_Meldungen[NOTI Created On] )
                && MS_MI_Meldungen[NOTI Type] = "MI"
        ),
        [RankMI]
    )
VAR _Result =
    IF (
        MS_MI_Meldungen[NOTI Type] = "MS",
        IF (
            MS_MI_Meldungen[SYS Serial Number] = _LastMISYS
                && MS_MI_Meldungen[NOTI Created On] > _LastCreatedtime
                && MS_MI_Meldungen[NOTI Created On] <= _LastCreatedtime + 28,
            _GroupMS
        ),
        _GroupMI
    )
RETURN
    _Result

Result is as below.

We can see that count for MI is correct and I group each MI and MS which are in the same calcualting. They will show as group1 , group2 ... groupN depends on the rank of MI.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[bagabo]

Hi @v-rzhou-msft !

First, thank you very much for your reply! 

Sadly it doesnt work as expected, as shown in the image. 

The count of MS per MI works just fine, but the grouping doesn't. Is it becauce of the variing positions in the table? As you can see in the index, it's not sorted. I tryed sorting it in Power Quey: Serial Number > Date, but in Power BI the date sorting gets lost and only the serial number sorting is left. 

Regards,
Gabriel

[v-rzhou-msft]

Hi @bagabo 

I think your data model will cause the issue. Let's see your table screenshot.

You want to count the value of MS in 28 days after the ”MI“ type and group them. However, we can see that you have many "MI" type with close time in your screenshot. 

For example in red box,  Created on of MI1 = 2019/09/30, Created on of MI2 = 2019/10/02. So Ms in 2019/10/04 , 2019/10/08 ...  will in both groups. So you may get the wrong result. 

To add a group number for MI, you can build a rank just like in my measure by rankx function.

RankMI = 
            RANKX (
                FILTER ( MS_MI_Meldungen, MS_MI_Meldungen[NOTI Type] = "MI" ),
                MS_MI_Meldungen[NOTI Created On],
                ,
                ASC
            )

This will only create an index by "NOTI Created On" for values whose "NOTI Type" = "MI".

Do you want to show all groups for the MS in different groups?

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[bagabo]

Hi @v-rzhou-msft, sorry for the late response.
I understand. So the in this special case, all of the MS should count to both of the MI. So like this should be a single Group, because both of the MIs have the same entrys. 

Regards, Gabriel