How create additional table based on specific status

[Boopep]

I want to create another table based on the premise that will include all the ClaimSeriesNumber and when there is a duplicate ClaimSeriesNumber only those with status Refiled will be included in the new table?

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[v-ssriganesh]

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.

[v-ssriganesh · Accepted solution]

Hi @Boopep,

I have reproduced your scenario in Power BI Desktop. You can achieve this by creating a new table that filters based on duplicates and status.

Here’s one way using DAX:

NewTable =

VAR WithCounts =

    ADDCOLUMNS (

        Claims,

        "CountPerSeries",

        CALCULATE ( COUNTROWS ( Claims ), ALLEXCEPT ( Claims, Claims[ClaimSeriesNumber] ) )

    )

RETURN

FILTER (

    WithCounts,

    [CountPerSeries] = 1 || ( [CountPerSeries] > 1 && Claims[Status] = "Refiled" )

)

This logic will:

• Keep all unique ClaimSeriesNumber rows.
• For duplicates, only include those where the Status = "Refiled".

I tested this with sample data and got the expected result:

For your reference, I am attaching .pbix file and thank you, @Ilgar_Zarbali , @MohamedFowzan1 & @Shahid12523 for sharing your valuable insights.

Best regards,
Ganesh Singamshetty.

[Ilgar_Zarbali]

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

[MohamedFowzan1]

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" )
)

[Shahid12523]

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"
)