Forum Discussion
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 ...
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.
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" )
)