Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

CALCULATE values from two tables, ignore data from Table A when Table B has value

Good day,

 

I have two tables. Table B is a SQL database, and Table A is a table where the user can insert manual data. 

The idea is that once in while the data in the SQL table is wrong, then user can insert the summarized value for a whole month. 

(Tables have several tag numbers)

 

Table A

DateTagvalue
31-01-20231

5000

31-01-202326000
28-02-202321000
   

 

Table B

DateTagValue
02-01-2023120
03-01-2023130
04-01-2023115
05-01-2023140
-1-
-1-
-1-
-1-
31-01-2023125

 

What I want, is to "ignore" values in Table B whenever Table A has a manual input.

 

I managed with assistance, to use below measure:

 

PM Consumption = 
SUMX (
    VALUES ( dim_date[Date]),
    COALESCE (
        CALCULATE (SUM ( Table A[Value])) ,
        CALCULATE ( MAX(Table B[Value]) - MIN( Table B[Value]) )

 

 

But this measure only replace the value on the 31-01-2023. I want the measure to "ignore" all data from Table B during january 2023, since there is a manual input in Table A. And only show the summarized value from Table A.

 

Thanks in advance.

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , for that you need have common dimension like date

     

    and then you can have measure like

     

    Sumx(Values(Date[Month Year]), calculate(if(not(Isblank(sum(TableA[Value]))) , sum(TableA[Value]), sum(TableB[Value]))))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your reply amitchandak 🙂

       

      First of all sorry I forgot to mention that I do have a date table. 

       

      Your solution partly works. I try to explain in below picture.

       

      I still see data logged from table B when I have data in table A. But the Total is correct. 

       

      Hope this makes sense. 

       

       

      TABLE A = 
SUM ( Fact_PrevEnergyDataAP[Value] ) / 1000
      TABLE B = 
( MAX ( Fact_EnergyData[Value] ) - MIN ( Fact_EnergyData[Value] ) ) / 1000
      Consumption = 
SUMX (
    VALUES ( dim_date[Month] ),
    CALCULATE ( IF ( NOT ( ISBLANK ( [TABLE A] ) ), [TABLE A], [TABLE B] ) )
)