topic Re: How create additional table based on specific status in Desktop

How create additional table based on specific status

Boopep — Wed, 27 Aug 2025 23:23:05 GMT

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?

Re: How create additional table based on specific status

Shahid12523 — Thu, 28 Aug 2025 02:18:09 GMT

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

Re: How create additional table based on specific status

MohamedFowzan1 — Thu, 28 Aug 2025 05:03:57 GMT

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

Re: How create additional table based on specific status

Ilgar_Zarbali — Thu, 28 Aug 2025 07:28:15 GMT

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)

In Power BI Desktop → Transform Data.
Duplicate your claims table.
Group by ClaimSeriesNumber (choose All Rows).
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".
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

Re: How create additional table based on specific status

v-ssriganesh — Thu, 28 Aug 2025 10:34:24 GMT

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.

Re: How create additional table based on specific status

v-ssriganesh — Sun, 31 Aug 2025 17:42:56 GMT

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.

Re: How create additional table based on specific status

v-ssriganesh — Wed, 03 Sep 2025 06:07:10 GMT

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.

Re: How create additional table based on specific status

v-ssriganesh — Sun, 07 Sep 2025 17:23:38 GMT

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.