Forum Discussion

AsNa_92's avatar
AsNa_92
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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BIswajit_Das's avatar
    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...
BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
1 year ago

Hello AsNa_92 
It would be better if you share some demo data in order to check the formats and to understand your requirement better.
And as per the mentioned above example;

1 - If you are loading the data using database queries then you can modify the column in the query and load the desired data.

or
2 - You can also create a calculated column with some conditions to get your required result column.

Thanks & Regards...

AsNa_92's avatar
AsNa_92
Resolver II
1 year ago

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:

DateCategoryValue
2024/09/01Red50
2024/09/01Yellow40
2024/09/01Black5
2024/10/01Red90
2024/10/01Black30

 

Output:

DateCategoryValue
2024/09/01Red50
2024/09/01Yellow40
2024/09/01Black5
2024/10/01Red90
2024/10/01Black30
2024/10/01Yellow0
  • BIswajit_Das's avatar
    BIswajit_Das
    Impactful Individual
    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...