Forum Discussion

Boopep's avatar
Boopep
Helper I
11 months ago
Solved

How create additional table based on specific status

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 ...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    11 months ago

    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.

Shahid12523's avatar
Shahid12523
Community Champion
11 months ago

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