Forum Discussion
Solved
Add Row if data missing from dataset
Hi Guys, How Can I add row (data) if the data from source is missing? For Instance, I have category with value (Red=50, Green=20, Black=5) in 2024/09/01 But in 2024/09/02 the data is (Red=30...
Hello AsNa_92
As per your requirement you need to create a calculated table cause here you're trying to add a row with the alternative result for category date wise.
So Try the below DAX to create a calculated table;RESULT_TABLE =ADDCOLUMNS(CROSSJOIN(DISTINCT('TABLE1'[Date]),DISTINCT('TABLE1'[Category])),"Value",COALESCE(LOOKUPVALUE('TABLE1'[Value], 'TABLE1'[Date], [Date], 'TABLE1'[Category], [Category]),0))ORThere's another way to get your desired result using M-Queries on transfer data but for that also you need to add another table .ORYou can modify your used database Query.Thanks & Regards...
Hi BIswajit_Das
Thanks for your reply, if I want to add column with condition how can i apply that?
Since I'm using oracle database table which refresh monthly.
Sample Data:
| Date | Category | Value |
| 2024/09/01 | Red | 50 |
| 2024/09/01 | Yellow | 40 |
| 2024/09/01 | Black | 5 |
| 2024/10/01 | Red | 90 |
| 2024/10/01 | Black | 30 |
Output:
| Date | Category | Value |
| 2024/09/01 | Red | 50 |
| 2024/09/01 | Yellow | 40 |
| 2024/09/01 | Black | 5 |
| 2024/10/01 | Red | 90 |
| 2024/10/01 | Black | 30 |
| 2024/10/01 | Yellow | 0 |
Hello AsNa_92
As per your requirement you need to create a calculated table cause here you're trying to add a row with the alternative result for category date wise.
So Try the below DAX to create a calculated table;
RESULT_TABLE =
ADDCOLUMNS(
CROSSJOIN(
DISTINCT('TABLE1'[Date]),
DISTINCT('TABLE1'[Category])
),
"Value",
COALESCE(
LOOKUPVALUE('TABLE1'[Value], 'TABLE1'[Date], [Date], 'TABLE1'[Category], [Category]),
0
)
)
OR
There's another way to get your desired result using M-Queries on transfer data but for that also you need to add another table .
OR
You can modify your used database Query.
Thanks & Regards...