Forum Discussion

andfx's avatar
andfx
Frequent Visitor
7 years ago
Solved

Help using matrix

Hi guys,

 

I have those databases for example:

 

categories.csv

id;name;category
1;fiat;ford
2;onix;gm
3;etios;toyota
4;macan;porsche
5;enzo;ferrari

history.csv

date;value;id
17/01/2019;1100;1
17/01/2019;1500;2
17/01/2019;700;3
17/01/2019;950;4
17/01/2019;230;5
18/01/2019;1100;1
18/01/2019;1500;2
18/01/2019;700;3
18/01/2019;950;4
18/01/2019;230;5
19/01/2019;1100;1
19/01/2019;1500;2
19/01/2019;700;3
19/01/2019;950;4
19/01/2019;230;5

predict_two.csv

date;value;id
20/01/2019;220;1
20/01/2019;500;2
20/01/2019;120;3
20/01/2019;230;4
20/01/2019;770;5

predict_one.csv

date;value;id
21/01/2019;830;1
21/01/2019;120;2
21/01/2019;250;3
21/01/2019;700;4
21/01/2019;430;5

I wish I could build this using matrix or maybe a table if it's possible:

 

 

I tried everything that I know, but I couldn't. I can't move data of predicts to history, because they need to be separated.

 

 

Thank you.

 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi andfx,

     

    You need to have a 1 to Many relationship wiht a single cross filter direction, then add the following measure to your data:

     

    Predict/history =
SWITCH (
    TRUE ();
    SUM ( History[value] ) <> 0; SUM ( History[value] );
    SUM ( Predict1[value] ) <> 0; SUM ( Predict1[value] );
    SUM ( Predict2[value] ) <> 0; SUM ( Predict2[value] );
    BLANK ()
)

    Be aware that this formula is assuming that predit 1 and predict 2 includes diferrente dates and they are also not on the history table.

     

    Result is below and attached PBIX file.

     

     

    Regards,

    MFelix

5 Replies

Replies have been turned off for this discussion
  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi andfx,

    I assume that the I'd of categories tables connects with the history and predicts, you should create a calendar table and make relationships between calendar table and the history and calendar and predictions tables based on the date.

    Then you just need to use the calendar table as you column headers ans place your historic and prediction on the values should give expected result.
    Regards
    MFelix
    • andfx's avatar
      andfx
      Frequent Visitor
      7 years ago

      I tried it.. Not worked.

       

      I made calendar

       

      Calendar = 
CALENDAR ( MIN ( history[date] ); MAX ( predict_one[date] ) )

      Did the relationships

       

       

      The result:

       

       

      Thank you.

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi andfx,

         

        You need to have a 1 to Many relationship wiht a single cross filter direction, then add the following measure to your data:

         

        Predict/history =
SWITCH (
    TRUE ();
    SUM ( History[value] ) <> 0; SUM ( History[value] );
    SUM ( Predict1[value] ) <> 0; SUM ( Predict1[value] );
    SUM ( Predict2[value] ) <> 0; SUM ( Predict2[value] );
    BLANK ()
)

        Be aware that this formula is assuming that predit 1 and predict 2 includes diferrente dates and they are also not on the history table.

         

        Result is below and attached PBIX file.

         

         

        Regards,

        MFelix