How create additional table based on specific status

Create a new table that includes:
- All rows with unique ClaimSeriesNumber
- Only rows with duplicate ClaimSeriesNumber where Status = "Refiled"

Use DAX like this:

FilteredClaims =
VAR SeriesCount =
ADDCOLUMNS (
SUMMARIZE ( Claims, Claims[ClaimSeriesNumber] ),
"Count", CALCULATE ( COUNTROWS ( Claims ) )
)
RETURN
FILTER (
Claims,
LOOKUPVALUE (
SeriesCount[Count],
SeriesCount[ClaimSeriesNumber],
Claims[ClaimSeriesNumber]
) = 1
|| Claims[Status] = "Refiled"
)

Hi Boopep 

You can do this by counting the distinct value and adding the status filter if more than 1:

NewTable = 
VAR _dupes = 
    ADDCOLUMNS(
        SUMMARIZE(Claims, Claims[ClaimSeriesNumber]),
        "DupCount", CALCULATE(COUNTROWS(Claims))
    )
RETURN
    FILTER(
        Claims,
        VAR CurrCount = 
            CALCULATE(
                COUNTROWS(Claims), 
                Claims[ClaimSeriesNumber] = EARLIER(Claims[ClaimSeriesNumber])
            )
        RETURN
            CurrCount = 1
            || (
                CurrCount > 1
                && Claims[Status] = "Refiled"
            )
    )

If you would not like to use the "Earlier" function incase your model is huge then try this:

NewTable =
FILTER (
    ADDCOLUMNS (
        Claims,
        "DupCount", CALCULATE ( COUNTROWS ( Claims ), ALLEXCEPT ( Claims, Claims[ClaimSeriesNumber] ) )
    ),
    [DupCount] = 1
    || ( [DupCount] > 1 && Claims[Status] = "Refiled" )
)

You want to create another table in Power BI that:

• Starts from your claims table.
• Keeps all ClaimSeriesNumber values.
• BUT: if a ClaimSeriesNumber has duplicates, then keep only those rows where Status = "Refiled".

In Power BI you have two options:

• Power Query (M) → for a physical table in the model.
• DAX (calculated table) → also a physical table, but created with DAX formulas.

A. Power Query (M)

1. In Power BI Desktop → Transform Data.
2. Duplicate your claims table.
3. Group by ClaimSeriesNumber (choose All Rows).
4. Add a custom column logic:
   

   * If the grouped table has only one row → expand all rows (keep it).

   If the grouped table has >1 rows → filter it down to Status = "Refiled".

5. Expand back to a flat table.

M code pattern:

let
Source = Claims,
Grouped = Table.Group(Source, {"ClaimSeriesNumber"},
{{"AllRows", each _, type table [ClaimSeriesNumber=..., Status=...]}}),
AddFiltered = Table.AddColumn(Grouped, "Filtered", each
if Table.RowCount([AllRows]) = 1
then [AllRows]
else Table.SelectRows([AllRows], each [Status] = "Refiled")
),
RemoveOthers = Table.RemoveColumns(AddFiltered,{"AllRows"}),
Expanded = Table.ExpandTableColumn(RemoveOthers,"Filtered",{"ClaimSeriesNumber","Status"})
in
Expanded

That gives you exactly the table you want.

B. DAX (Calculated Table)

If you prefer DAX, create a new table:

FilteredClaims =
VAR WithCounts =
ADDCOLUMNS (
Claims,
"SeriesCount", CALCULATE ( COUNTROWS ( Claims ), ALLEXCEPT ( Claims, Claims[ClaimSeriesNumber] ) )
)
RETURN
FILTER (
WithCounts,
-- Keep all singletons
[SeriesCount] = 1
||
-- If duplicates, keep only Refiled
( [SeriesCount] > 1 && Claims[Status] = "Refiled" )
)

This gives you a new physical table in the model.

Result:

• If a ClaimSeriesNumber appears only once → that row stays regardless of status.
• If it appears multiple times → only the “Refiled” rows remain.

I hope it will help

Hello Boopep,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

Hello Boopep,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

Hello Boopep, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.