Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Always Fixed X axis

Hi Experts,

 

I wants to use fixed X-axis on Power BI.

For example, I have two tables like below.

 

[Category Table]

CategoryCategory Order
A1
B2
C3
D4
E5

 

[Fact Table]

ProductCategoryDate
P1B2020-05-11
P1A2020-03-10
P1E2020-06-27
P2C2020-01-03
P2E2020-04-30
P3D2020-05-05
P3A2020-02-17
P3C2020-04-09

 

The category table has all of the category and all the category in the table should always be shown on graph as X-axis,

even thogh in the fact table, for example, P1 doesn't have category: C, D.

The empty value should be shown as blank on the C and D axis.

Actually, the graph is linear, so the line will be cut on the blank X axis and will keep going on further X axis that has value.

 

More worse, It is not possible to insert value into fact table on the empty category.

 

Is there any way to make it possible given situation?

Thanks in advance.

 

Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous on x-axis where you dropped the category column, click arrow next to it and select show items with no data

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks, I tried as you gave a tip but it still doesn't work.

         

        The whole category only exists in Category table, and Main table that has date value doesn't have the whole category.

        I tried to put the Category column from Category table and Main table, but neither worked.  

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

    Hi Anonymous 

    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
    Do you accept to create another table to help realize your requirement?
     
    Best Regards
    Maggie
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    If i understand you correctly, you need to replace blank value with some specific values.

    For example, for p1, it don't has dates for "c" and "d", we can fill dates for "c" and "d" with the last date(date for "b").

    To achieve this, you need go to Transform data, create a new query:

     

    let
    Source = Table.SelectColumns(Table1,{"Category"}),
    #"Added Custom" = Table.AddColumn(Source, "Product", each List.Distinct(Table2[Product])),
    #"Expanded Product" = Table.ExpandListColumn(#"Added Custom", "Product"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Product", {"Category", "Product"}, Table2, {"Category", "Product"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Date"}, {"Table2.Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table2",{{"Product", Order.Ascending}, {"Category", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Table2.Date"})
in
    #"Filled Down"

     

    Close&&apply

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

    Hi 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie