Forum Discussion
Duplicating Rows in a Table in Modelling Tab
In my formula.
Table 2 , I removed pending and added data. In table 3, I removed pending, Also check if pending exists, Hope you taken care of that in your formula, Or in visualization, put a visual level filter to remove pending.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Thanks a lot for your help....
I think the source of that error is the line I highlighted in red colour in the Union where no filter is applied to remove the 'Pending'.
I did some modifications to that and it worked fine.
Table 3 = UNION(SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount ])),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])So my modifications and final code by adding FILTER with CALCULATETABLE is as per below :
Table 3 =
UNION(
CALCULATETABLE(
SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount])),
FILTER(duplicaterow,duplicaterow[Approval Month]<>"Pending")),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])
)Regards
Check the link
https://www.dropbox.com/s/rbfswh4nbre14mk/duplicaterow.pbix?dl=0
Table 2 = SUMMARIZE(FILTER(duplicaterow,duplicaterow[Approval Month]<>"Pending" && COUNTX(filter(duplicaterow,duplicaterow[Approval Month]="Pending"),duplicaterow[RowCount ])>=1),duplicaterow[Approval Month],"Row Count",SUM(duplicaterow[RowCount ]),"New Column","Pending")
Table 3 = UNION(SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount ])),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Many thanks for your prompt response...
the result is almost there with the tips you gave, but I got one row in that result which needs to be eliminated, there's the row that shows 'Pending " in the 'New Column'
Colud you have a look? i dont need that row in the result
Approval Month RowCount New Column
2019-09 1 2019-09
2019-10 1 2019-10
2019-11 1 2019-11
2019-12 1 2019-12
Pending 5 Pending
2019-08 1 2019-08
Pending 1 2019-09
Pending 1 2019-10
Pending 1 2019-11
Pending 1 2019-12
Pending 1 2019-00
Thanks
Lanrex
In my formula.
Table 2 , I removed pending and added data. In table 3, I removed pending, Also check if pending exists, Hope you taken care of that in your formula, Or in visualization, put a visual level filter to remove pending.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Thanks a lot for your help....
I think the source of that error is the line I highlighted in red colour in the Union where no filter is applied to remove the 'Pending'.
I did some modifications to that and it worked fine.
Table 3 = UNION(SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount ])),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])So my modifications and final code by adding FILTER with CALCULATETABLE is as per below :
Table 3 =
UNION(
CALCULATETABLE(
SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount])),
FILTER(duplicaterow,duplicaterow[Approval Month]<>"Pending")),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])
)Regards