Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |