The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello, I'm new to power BI and now I'm facing a problem with missing data.
I've got a table recorded the growth rate of different kinds of virus as follow:
What I want to do is to create a new table and adding rows to the time point with missing data, and assuming that the growth rate remain the same as the previous reading until the next reading occurred. Like the following table:
I've created a new table with Unique Time point in each trial,
I want to know if it's possible to add missing data at corresponding time point in Power BI.
Any hints or advises would be much appreciated!
Solved! Go to Solution.
Hi @hokitkan,
You can create a table below:
Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))
Then create a calculated column below:
Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0
Best Regards,
Qiuyun Yu
Hi @hokitkan,
You can create a table below:
Table 2 = UNION(ADDCOLUMNS(EXCEPT(CROSSJOIN(SUMMARIZE(Table1,[Colum1],[Colum2]),VALUES('Table'[Column4])),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4])),"Column3",0),SELECTCOLUMNS(Table1,"Colum1",[Colum1],"Colum2",[Colum2],"Column4",[Column4],"Column3",[Column3]))
Then create a calculated column below:
Amount = if([Column3]<>0,[Column3], MAXX(FILTER(ALL('Table 2'),[Colum1]=EARLIER([Colum1])&&[Colum2]=EARLIER('Table 2'[Colum2])&&[Column4]<EARLIER([Column4])&&[Column3]<>0),[Column3]))+0
Best Regards,
Qiuyun Yu
It works perfectly! Thanks for your efforts!
User | Count |
---|---|
80 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |