问题

我正在尝试获取DAX公式，以获取客户与我们合作的连续年数。同一客户（注册号）可能在同一年内有多次记录。如果年份之间存在间隙，则计数将重置。附有示例数据和预期结果。

我已经根据此资源修改了公式：https://community.fabric.microsoft.com/t5/Desktop/Calculating-Consecutive-Years-Active/td-p/1670339，但公式的结果不是我预期的：

连续年数 = 
VAR vMaxYear =
    MAX ( 'Data: Policy'[Effective year] )
VAR vThisCustomer =
    MIN ( 'Data: Policy'[Transformed Register number])
VAR vLatestNotDonation =
    MAXX (
        FILTER (
            ALL ( 'Data: Policy'[Effective year]),
            ISBLANK (
                CALCULATE (
                    COUNTROWS ( 'Data: Policy' ),
                    'Data: Policy'[Transformed Register number] = vThisCustomer
                )
            )
        ),
        'Data: Policy'[Effective year]
    )
VAR vLatestIfBlank =
    IF (
        ISBLANK ( vLatestNotDonation ),
        MINX (
            ALL ( 'Data: Policy'[Effective year] ),
            'Data: Policy'[Effective year]
        ) - 1,
        vLatestNotDonation
    )
RETURN
    IF (
        vMaxYear = 2023,
        vMaxYear - vLatestIfBlank
    )

|Effective year |Register number |Formula Outcome |Expected ConsecutiveYears|
|:---- |:-----:|----:|
|2017 |AX1234 |0 |1|
|2018 |AX1234 |0 |2|
|2020 |AX1234 |0 |1|
|2020 |AX1234 |0 |1|
|2021 |AX1234 |0 |2|
|2022 |AX1234 |1 |3|
|2023 |AX1234 |1 |4|
|2023 |AX1234 |1 |4|
|2023 |AX1234 |1 |4|
|2023 |AX1234 |1 |4|
|2023 |AX1234 |1 |4|

感谢您的帮助。非常感谢。

Consecutive Yrs = 
VAR vMaxYear =
    MAX ( 'Data: Policy'[Effective year] )
VAR vThisCustomer =
    MIN ( 'Data: Policy'[Transformed Register number])
VAR vLatestNotDonation =
    MAXX (
        FILTER (
            ALL ( 'Data: Policy'[Effective year]),
            ISBLANK (
                CALCULATE (
                    COUNTROWS ( 'Data: Policy' ),
                    'Data: Policy'[Transformed Register number] = vThisCustomer
                )
            )
        ),
        'Data: Policy'[Effective year]
    )
VAR vLatestIfBlank =
    IF (
        ISBLANK ( vLatestNotDonation ),
        MINX (
            ALL ( 'Data: Policy'[Effective year] ),
            'Data: Policy'[Effective year]
        ) - 1,
        vLatestNotDonation
    )
RETURN
    IF (
        vMaxYear = 2023,
        vMaxYear - vLatestIfBlank
    )

答案1

得分: 2

I think I found a better way without applying so many transformations and that's why I have labeled my earlier solution as Solution-2 and labeled the new solution as Solution-1.

Solution-1

First create a Measure as below-

check_break_measure = 

var cr_year = min(your_table_name[Effective year])
var cr_reg_number = MIN(your_table_name[Transformed Register number])

var prev_year = CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] < cr_year
            && your_table_name[Transformed Register number] = cr_reg_number
    )
)

return if(ISBLANK(prev_year) || cr_year - prev_year >= 2, 1,0)

And then the second Measure as below-

serial_no = 

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group = 
CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[check_break_measure] = 1
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

var sl = 
CALCULATE(
    DISTINCTCOUNT(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[Effective year] >= break_year_current_group
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

Return sl

Both above and below solution you will get the same output

Solution-2

If you can do some transformation in your table in the power query editor and then create a Measure, you can achieve the requirement.

First, apply these below code to your table's Advance Editor-

let
    //Existing Steps,
    

    //new steps started from here
    //please edit "your_previous_step_name" properly in the below line with your actual your_previous_step_name

    #"Sorted Rows1" = Table.Sort(#"your_previous_step_name",{{"Transformed Register number", Order.Ascending}, {"Effective year", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Effective year"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Index_", each [Index]+1),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Index", "Index_", "Transformed Register number", "Effective year"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"Index"}, #"Reordered Columns1", {"Index_"}, "Reordered Columns1", JoinKind.LeftOuter),
    #"Expanded Reordered Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns1", {"Transformed Register number", "Effective year"}, {"Reordered Columns1.Cat", "Reordered Columns1.Year"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Reordered Columns1",{{"Reordered Columns1.Cat", "pr_cat"}, {"Reordered Columns1.Year", "pr_year"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Index", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "check_break", each if [Transformed Register number] <> [pr_cat] or [Effective year] - [pr_year] >=2 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index_", "pr_cat", "pr_year"})
in
    #"Removed Columns"

Considering your sample data, you will have data as below now-

Now go to the Report designer and create a new Measure with this below code-

serial_no = 

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group = 
CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[check_break] = "1"
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

var sl = 
CALCULATE(
    DISTINCTCOUNT(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[Effective year] >= break_year_current_group
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

Return sl

Your final data output will be as below-

I hope this helps!

<details>
<summary>英文:</summary>

I think I found a better way without applying so many transformations and that&#39;s why I have labeled my earlier solution as **Solution-2** and labeled the new solution as **Solution-1**.


**Solution-1**

First create a **Measure** as below-

check_break_measure =

var cr_year = min(your_table_name[Effective year])
var cr_reg_number = MIN(your_table_name[Transformed Register number])

var prev_year = CALCULATE(
max(your_table_name[Effective year]),
FILTER(
all(your_table_name),
your_table_name[Effective year] < cr_year
&& your_table_name[Transformed Register number] = cr_reg_number
)
)

return if(ISBLANK(prev_year) || cr_year - prev_year >= 2, 1,0)

And then the second **Measure** as below-

serial_no =

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group =
CALCULATE(
max(your_table_name[Effective year]),
FILTER(
all(your_table_name),
your_table_name[Effective year] <= current_row_year
&& your_table_name[check_break_measure] = 1
&& your_table_name[Transformed Register number] == current_row_reg_number
)
)

var sl =
CALCULATE(
DISTINCTCOUNT(your_table_name[Effective year]),
FILTER(
all(your_table_name),
your_table_name[Effective year] <= current_row_year
&& your_table_name[Effective year] >= break_year_current_group
&& your_table_name[Transformed Register number] == current_row_reg_number
)
)

Return sl

**Both above and below solution you will get the same output**

-----
**Solution-2**

If you can do some transformation in your table in the power query editor and then create a Measure, you can achieve the requirement.

First, apply these below code to your table's **Advance Editor**-

let
//Existing Steps,

//new steps started from here
//please edit "your_previous_step_name" properly in the below line with your actual your_previous_step_name

#"Sorted Rows1" = Table.Sort(#"your_previous_step_name",{{"Transformed Register number", Order.Ascending}, {"Effective year", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Effective year"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Index_", each [Index]+1),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Index", "Index_", "Transformed Register number", "Effective year"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"Index"}, #"Reordered Columns1", {"Index_"}, "Reordered Columns1", JoinKind.LeftOuter),
#"Expanded Reordered Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns1", {"Transformed Register number", "Effective year"}, {"Reordered Columns1.Cat", "Reordered Columns1.Year"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Reordered Columns1",{{"Reordered Columns1.Cat", "pr_cat"}, {"Reordered Columns1.Year", "pr_year"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Index", Order.Ascending}}),
#"Added Custom1" = Table.AddColumn(#"Sorted Rows", "check_break", each if [Transformed Register number] <> [pr_cat] or [Effective year] - [pr_year] >=2 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index_", "pr_cat", "pr_year"})

in
#"Removed Columns"

Considering your sample data, you will have data as below now-

[![enter image description here][1]][1]

Now go to the Report designer and create a new **Measure** with this below code-

serial_no =

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group =
CALCULATE(
max(your_table_name[Effective year]),
FILTER(
all(your_table_name),
your_table_name[Effective year] <= current_row_year
&& your_table_name[check_break] = "1"
&& your_table_name[Transformed Register number] == current_row_reg_number
)
)

var sl =
CALCULATE(
DISTINCTCOUNT(your_table_name[Effective year]),
FILTER(
all(your_table_name),
your_table_name[Effective year] <= current_row_year
&& your_table_name[Effective year] >= break_year_current_group
&& your_table_name[Transformed Register number] == current_row_reg_number
)
)

Return sl

Your final data output will be as below-

[![enter image description here][2]][2]


  [1]: https://i.stack.imgur.com/UCP5o.png
  [2]: https://i.stack.imgur.com/fPzQp.png

</details>