Forum Discussion

Resolver II

1 year ago

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...

BIswajit_Das
1 year ago
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...

Joe_Barry

Solution Sage

1 year ago

Hi AsNa_92

There are various ways of doing it depending on your source, but in Power Query, I would create a new table that has the same columns as the existing one or at least the columns that you need. You can do this by using the Enter Data function in the Ribbon. Make sure that the column names match that of the other table, watch out for lower & upper case. Fill in the row with Green 0 and what ever the other columns you have.

When you are finished go to the original table and choose Append table and choose the table you just created. It will now be part of that table.

Regarding that it's static for all days in the future, this depends on the columns you have in the original table.

In DAX you could create a running total measure that will populate a visual or a table based on the date range you have chosen.

Running Total = 
VAR _MaxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
    SUM('Table'[Amount]), ///Is your table showing a culmative value? Then use MAX instead of SUM
    FILTER( ALL('Calendar'),
   'Calendar'[Date] <= _MaxDate
    )

Add this measure to a visual along with the columns from your calendar table that you want to use for showing the date and the colour column from your table and you will egt the result you want