Using Calculated Measure as a value in a Line Chart

[Suma]

Hi All,

I am new to Power BI, learning basics to migrate from excel to BI.

I've the data (history of Incidents from April) coming up from Excel which is in a Table (Incident_AO). 2 columns of this table - Date and count of incidents on a particular day. Please see below. I've added a Line chart so it shows the Trend.

Date      Incident_AO

5-Apr         100

6-Apr         115

7-Apr         155

....

25-Oct        400

I've a different table connected to an excel sheet again which is the extract of Incidents. Let say for tomorrow if i need to update the count of incidents, I've written a measure to calculate the count (355)

However, I need to show my trend in the same above graph the count 355.

26-Oct      355

The Line comes but its not continuation after 400. Chart should show a dip from 400 to 355 and go on based on daly incident count. I tried calculating columns it didn;t work. Please suggest how do i resolve this issue.

Thanks,

[v-juanli-msft]

Hi @Suma

I've the data (history of Incidents from April) coming up from Excel which is in a Table (Incident_AO),

I've a different table connected to an excel sheet again which is the extract of Incidents. Let say for tomorrow if i need to update the count of incidents, I've written a measure to calculate the count (355)

From above, i conclude :

1.Incident_AO table contains historical data which can't update later.

2.a different table connected to an excel sheet again which is the extract of Incidents.

this table(called table2) connect to the Incident_AO table based on "date" column, this table can update, for example, 2018/10/26, this table add a new row of 

date                 count of incidents

2018/10/26         355

why you don't use the table2 to create a line chart is that this table doesn't has much historical data as Incident_AO table does.

Thus, i has a workaround, create a new table in power bi with the following dax

new = DISTINCT(UNION(Incident_AO,table2))

when update table2(enter new data in original table, then click refresh from power bi desktop), then this new table will fresh with the lastest data.

Best Regards

Maggie

[Suma]

Maggie - Thanks so muh for your response first of all. Just to update you....

10/26/2018         355   (Table 2) -> Practically this is the extract from Service now with more than 20 columns and count of 355 is not straight forward. This count is based on some filters applied. Like column 10= "abc", Column 8="xyz" etc.

Did you asked me to write a measure in the "table 2" OR a "new table" with a measure below?

new = DISTINCT(UNION(Incident_AO,table2))

Please clarify.

[v-juanli-msft]

Hi @Suma

write a formula to create a "new table".

new table= DISTINCT(UNION(Incident_AO,table2))

Best Regards

Maggie