Set values in a visual (table visual) from 2 different model of table

[Anonymous]

Greeting all ,  i have the following tables as my data source (Live connection with a cube ) (1: table with current values , and a table with historic values )

Ex --First table (Historique )  :    

KPI name	Value	Month name
KPI A	10	mars
KPI A	15	avril
KPI A	20	mai
KPI B	1	mars
KPI B	5	avril
KPI B	2	mai
KPI C	100	mars
KPI C	200	avril
KPI C	300	mai

table with the Current values ( Current and last months ) 

KPI A	KPI B	KPI C	KPI X	Month
25	9	400	20	June
30	3	500	25	July
			15	May
			10	April
			5	Mars

=> what im trying to make is a table with those values (Both historic and current ) : 

Objective :  Table Overview : 

	Mars	April	may	June	July
KPI A	10	15	20	25	30
KPI B	1	5	2	9	3
KPI C	100	200	300	400	500
KPI X	5	10	15	20	25

So please How can i  create the table overview using the historic and the current table (the KPI X doesnt exist in the historic table ) Thanks in advance community !  

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

The live connection mode cannot enter data and create a calculate table. And this issue cannot use one measure to achieve.

So you need to add a table as follows in the data source. Please be careful not to build a relationship.

Then we can create a measure and build the matrix table like this,

Measure = 
VAR _table1 =
    SELECTCOLUMNS (
        'Current',
        "KPI", "KPI A",
        "value", CALCULATE ( SUM ( 'Current'[KPI A] ) ),
        "Month", 'Current'[Month]
    )
VAR _table2 =
    SELECTCOLUMNS (
        'Current',
        "KPI", "KPI B",
        "value", CALCULATE ( SUM ( 'Current'[KPI B] ) ),
        "Month", 'Current'[Month]
    )
VAR _table3 =
    SELECTCOLUMNS (
        'Current',
        "KPI", "KPI C",
        "value", CALCULATE ( SUM ( 'Current'[KPI C] ) ),
        "Month", 'Current'[Month]
    )
VAR _table4 =
    SELECTCOLUMNS (
        'Current',
        "KPI", "KPI X",
        "value", CALCULATE ( SUM ( 'Current'[KPI X] ) ),
        "Month", 'Current'[Month]
    )
VAR _new_table =
    FILTER ( UNION ( _table1, _table2, _table3, _table4 ), [value] <> BLANK () )
VAR _table =
    UNION ( _new_table, HIS )
VAR _result =
    SUMX (
        FILTER (
            _table,
            [KPI] = MAX ( 'Table'[KPI] )
                && [Month] = MAX ( 'Table'[Month] )
        ),
        [value]
    )
RETURN
    _result

Live Connection has many limitations, we recommend that you process your data at the data source.

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

Thank you so much !  it works ! 

i find another trick with  Dax to implement the table using the IF function : 

Measure = if(Month(ENDOFMONTH(DimDate[Date])) = month(today())  , -get values from current table-  ,  calculate (Values from historic table) 

i need for this a dimension table . after ,  all i need to do is to set the Month year and measure in one table .

[az38]

@Anonymous 

hi

try this technique https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

[v-zhenbw-msft]

Hi @Anonymous ,

We can unpivot Current table and append the current table and history table to meet your requirement.

1. Select month column and unpivot other columns.

2. Then we need to change the column name, because append function needs two tables have the same column name.

3. At last we append two tables and create a matrix table to get the result.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

Thank you so much for your explanation but as i mention in the first line the mode of connection is in live mode with a cube , so i cant use the unpivot method . 

is there a Dax funtion that can do the unpivot method ? 

Thanks in advance