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?   ...
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

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

  • OskariNi's avatar
    OskariNi
    Helper I
    8 years ago

    Hi MFelix

     

    The hierarchies (date and type) are in the same table (small part of the table in the first post). I can't share my pbix but perhaps I can generate a similar sample data for you. 

    • MFelix's avatar
      MFelix
      Super User
      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