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