Forum Discussion

Andvil's avatar
Andvil
Helper V
6 years ago
Solved

Filters or connections Issue

Hello,

Im trying to do a dynamic market share report with sales from 2018, 2019 and 2020.  I have the information separated between the three years and I have uploaded each year individually to Power Bi. Therefore, I have 3 pie charts according to the previous years I mentioned. I want to filter those pie charts according to two categories (Documents or Packages) to see who are the market leaders in each category. I also want to filter according to months, to check how market share changed every month in each year. Finally I also want to filter according to the company

 

As mentioned, data from each year is separated. I tried creating a calendar table and a category table and connect them to the columns of the sales data, it worked but for only one connection and when creating another connection I receive an error message saying that a direct connection cant be created. I was considering appending queries but my original report has A LOT of data and I do not know if this would be the best solution.

 

I attach a Power Bi example with data so you can help me: DELETED

 

As a summary, I want the filters to affect the pie charts from the three years.

 

Thank you very much for any advice or help.

 

Best, 

JL

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Andvil 

    Since there is no [Date] field in 2018, 2019 and 2020, and just there are [Year] and [Month] in them. 

    So you could adjust your model as below:

    Step1:

    Create a YearMonth column in 2018, 2019 and 2020.

    Step2:

    Summarize CALENDAR TABLE that only contains [Year],[Month],[MonthNameShort],[Quarter],[YearMonth ]

    CALENDAR TABLE = 
SUMMARIZE(ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Month",SWITCH(MONTH([Date]),
1,"January",
2,"February",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December")),
[Year],[Month],[MonthNameShort],[Quarter],"YearMonth",[Year]&"-"&[Month])

     

    Step3:

    Now create relationship by YearMonth column instead of Year column

     

    here is sample pbix file,please try it.

     

     

    Regards,

    Lin

  • MFelix's avatar
    MFelix
    6 years ago

    Hi Andvil ,

     

    You need to create a blank query and then add the following code:

    = Table.Combine({#"2018", #"2019", #"2020"})

     

    This will create a single table with all 3 datasets then add the following calculated column on the query editor:

    Date.EndOfMonth(

#date([Year], (if [Month] = "January" then 1 else
if [Month] = "February" then 2 else
if [Month] = "March" then 3 else
if [Month] = "April" then 4 else
if [Month] = "May" then 5 else
if [Month] = "June" then 6 else
if [Month] = "July" then 7 else
if [Month] = "August" then 8 else
if [Month] = "September" then 9 else
if [Month] = "October" then 10 else
if [Month] = "November" then 11 else
if [Month] = "December" then 12 else
0), 1))

    Now go to each of the 3 previous tables rigth click and disable the option Enable load, this will make that tables will not be uploaed to your front end.

     

    Now just make the relationship between the date column and endofmonth column and you are all set.

     

    Check PBIX file attach, be aware that you need to change the souce file location for the 2018, 2019 and 2020 to the correct place since I have made a local file to make this work.

     

    Any questions please tell me.

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Andvil ,

     

    Having the information spreadout into 3 different tables is not the best option. especially since the information is always the sames format you should make a single table.

     

    To make this and based on the information you have you should make it using Files from Folder. Then you can adjust the created to make it for onedrive files (based on the name of the folder you are using I assume you will have files in onedrive)

     

    https://support.office.com/en-us/article/combine-files-in-a-folder-with-combine-binaries-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4

     

    Then using the calendar table everything will be correct.

     

    Taking into account that I don't have access to the files I have made a new table in DAX that join all the tables however you need to do it using the link above.

     

    You also need to create a Endofmonth column to link to your calendar table the result is on PBIX file attach.

     

    The report with the name WITH A SINGLE TABLE is the one you need to look at.

     

    If you need help to make the single table please tell me.

     

    Be aware file was saved in April 2020 version of PBI.

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      Hello MFelix,

       

      First of all, thank you for your answer and help.

       

      I have realized that having the information in different tables is not the best option. I am not the manager of the data and the original data is extremely big and it is updated monthly.  The user that manages the data created a connection to SQL and sends it to me in different tables. Due to the size of the information, it is impossible to have it in a folder as you are suggesting.

       

      Do you know another solution considering this conditions? As I mentioned at the description of the problem, i was considering appending the tables but I do not know if that would be the best solution.

       

      I have checked the example you provide and that is exactly what I need. Thank you in advance for your comments.

       

      Best,

       

      Jose L

       

       

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Andvil ,

         

        If that is not an option you can make the merge the tables on the query editor and then only make the upload of the final table.

        I will prepare a sample file and send it to you with the explanation.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Andvil 

    Since there is no [Date] field in 2018, 2019 and 2020, and just there are [Year] and [Month] in them. 

    So you could adjust your model as below:

    Step1:

    Create a YearMonth column in 2018, 2019 and 2020.

    Step2:

    Summarize CALENDAR TABLE that only contains [Year],[Month],[MonthNameShort],[Quarter],[YearMonth ]

    CALENDAR TABLE = 
SUMMARIZE(ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(2020,12,31)),
"Year", YEAR ( [Date] ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Month",SWITCH(MONTH([Date]),
1,"January",
2,"February",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December")),
[Year],[Month],[MonthNameShort],[Quarter],"YearMonth",[Year]&"-"&[Month])

     

    Step3:

    Now create relationship by YearMonth column instead of Year column

     

    here is sample pbix file,please try it.

     

     

    Regards,

    Lin

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      Thank you v-lili6-msft it worked!! MFelix I want to thank you for your help too, your ideas helped me to figure out a big part of the solution.

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      Hi v-lili6-msft,

       

      I have another problem, after doing the connections as you said, what if I want to have a table with the sales of the three years and then filter it. Therefore, I will have the table with the total of sales from 2018 to 2020 and then filter each year with a slicer to analyze different years. Do I necessarily need an appended table with all the sales?

       

      Because in your example, I have to have the table and add the sales of each year independently. When adding the 2020 sales, and filtering with another year I have the value 0.

       

      What i would like to have is to create a report with the total sales and then filter year by year.

       

      I did what MFelix told me, to append the tables but now my file is very heavy. I assume it is because i have the information twince in my report (2018 to 2020 and the appended table).

       

      Best,

      JALV

       

       

       
      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Andvil,

         

        You can make the append of the tables in a single query however that need you to make some advance copy paste.

         

        Do you want some guidance on that? 

    • Andvil's avatar
      Andvil
      Helper V
      6 years ago

      Hello @v-lili6-msft,

      I have another problem, after making the connections as you said, what if I want to have a table with the three-year sales and then filter it. Therefore, I will have the table with total sales from 2018 to 2020 and then filter each year with a segmentation to analyze different years. Do I necessarily need an attached table with all sales?

      Because in your example, I have to have the table and add the sales each year independently. When you add 2020 sales, and filter with another year I have the value 0.

      What I'd like to have is to create a report with the total sales and then filter year by year.

      I did what @MFelix told me, to append the tables, but now my file is very heavy. I guess it's because I have the twince information in my report (2018 to 2020 and the table attached).

      Best

      JALV