Forum Discussion

OskariNi's avatar
OskariNi
Helper I
8 years ago
Solved

Clusterd bar chart using 2 x-axis columns

Hi all, 

 

I would like to combine these 2 charts into 1 clustered chart. The problem is that they use different columns for the week values (x-axis). Is this possible with power BI?

 

 

Thanks, Oskari

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi OskariNi,

     

    Make a Dimension table with Week and then make the relatioship between this table and the Open and Close date be aware that for this two relationships you need to make inactive relationships.

     

    Then create two measures like this:

     

    Opened = CALCULATE ( COUNT(Table[Open Week]); USERELATIONSHIP(Table[Open Week]; Week[Week])


Closed= CALCULATE ( COUNT(Table[Closed Week]); USERELATIONSHIP(Table[Closed Week]; Week[Week])

     

    Then create your chart using the table from the week column in the axis and both measures on values.

     

     

    Be aware that your week table probably should be YearWeek and not only week.

     

    Regards,

    MFelix

     

  • MFelix's avatar
    MFelix
    8 years ago

    Hi OskariNi,

     

    One best practice is to create a Dimension table for Dates making use of all the hierarchies trhough that table, this is applicable when you have several tables or fields within the same table that you need to connect by date field.

     

    Check this example or search for DimDate / Calendar in Power BI models, you will get several examples then you just need to implement the solution I have refer.

     

    Regards,

    MFelix

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi OskariNi,

     

    Make a Dimension table with Week and then make the relatioship between this table and the Open and Close date be aware that for this two relationships you need to make inactive relationships.

     

    Then create two measures like this:

     

    Opened = CALCULATE ( COUNT(Table[Open Week]); USERELATIONSHIP(Table[Open Week]; Week[Week])


Closed= CALCULATE ( COUNT(Table[Closed Week]); USERELATIONSHIP(Table[Closed Week]; Week[Week])

     

    Then create your chart using the table from the week column in the axis and both measures on values.

     

     

    Be aware that your week table probably should be YearWeek and not only week.

     

    Regards,

    MFelix

     

    • OskariNi's avatar
      OskariNi
      Helper I
      8 years ago

      Thank's MFelix,

       

      This works but I lose the hierarchy I have with the weeks (Dates, type etc...). 

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi OskariNi,

         

        Does the hierarchies you have is in a separate table or in the same table?

         

        If it's in a separate table you should use that table and make the USERELATIONSHIP and the inactive connection with the column that isn't connected yet.

         

        You don't have a lot of information about your model so it's difficult to give more than a generic answer that needs to be adapted, can you share a PBIX or a sample data and expected result?

         

        Regards,

        MFelix

  • CosminIr's avatar
    CosminIr
    Frequent Visitor
    7 years ago

    Hi all,

     

    I want also to combine 2 charts into one. The first one is filtered by the shop and the second is not filtered. I'm new to Power Bi :) Please help.

    Thanks for your time.

    Cosmin