Always Fixed X axis

[Anonymous]

Hi Experts,

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

For example, I have two tables like below.

[Category Table]

Category	Category Order
A	1
B	2
C	3
D	4
E	5

[Fact Table]

Product	Category	Date
P1	B	2020-05-11
P1	A	2020-03-10
P1	E	2020-06-27
P2	C	2020-01-03
P2	E	2020-04-30
P3	D	2020-05-05
P3	A	2020-02-17
P3	C	2020-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.

[v-juanli-msft]

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

[v-juanli-msft]

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]

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

[parry2k]

@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!

[parry2k]

[Anonymous]

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]

Hi @Anonymous 

I can't add dates in the y axis as your did.

Best Regards

Maggie