Continous X Axis with data (integers) from two different tables

[GregoryMartin]

Hello there, me again,

Solved my last problem with continuous X-axis involving dates from two different table with the CALENDARAUTO() functionnality (thanks again to @jthomson) but I'm now having the same issue with a similar X-axis with interger values instead of dates. Is there a CALENDARAUTO()-like functionnality working with columns containing integers ?

Have you got some magic tricks in your hat to help me with that problem ?

[v-jiascu-msft]

Hi @GregoryMartin,

If you have the integers in different tables, you can bring them together in a new table like this.

Result =
DISTINCT ( UNION ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) )

Best Regards,

Dale

[GregoryMartin]

Before all, thank you both for your quick answers.

@jthomson : I juste can't found the function you told me about. Power BI only give me the choice between GENERATE and GENERATEALL (as seen on th screenshot). GENERATESERIES is a function given with a plug-in ?

@v-jiascu-msftI'd already tried to do this way but I couldn't create the relation between this new table and the two others, due tu a non unique values column.

[v-jiascu-msft · Accepted solution]

Hi @GregoryMartin,

Filtering out the blanks could be helpful.

Result =
FILTER (
    DISTINCT ( UNION ( VALUES ( Table1[ID] ), VALUES ( Table2[ID] ) ) ),
    [ID] <> BLANK ()
)

Best Regards,

Dale

[GregoryMartin]

Hi @v-jiascu-msft,

Thank you so much ! There was my problem. I was absolutly certain that the DISTINCT funtion was concerning also the 'blank' value and that the table will consider that blank value as unique. Didn't thought of that kind of problem.

Again thank you very much. Problem solved.

[jthomson]

You can probably try something similar using the GENERATESERIES function, if you are able to work out the highest and lowest values of your two tables (or know what sort of values you expect them to be), you can plug these in and it'll make a table of integers that you can relate your data to and use the value column as a common link for your axis