Unable to use measure on Axis field

[TomBLG]

Hello everyone.

I'm relatively new to Power BI and was hoping someone could give me a hand.

I'm building a dashboard to visualise electrical meter data in Hourly, Daily and Weekly formats. I have Date columns for Hourly, Daily and Weekly.

Hourly: yyyy-mm-dd hh:mm:ss

Daily: yyyy-mm-dd

Weekly: yyww (YearWeek, 2101, 2102, 2103, 2104, etc.)

The values in the graph come from a measure pulling data from 3 different sources depending on the periodicity selection. I made the same measure for Dates hoping it would work and... here I am.

Timestamp Selection =

SWITCH( TRUE(),

    VALUES('Control Table'[Periodicity]) = "Hourly", MAX('Unpivot Hourly'[Hourly]),

    VALUES('Control Table'[Periodicity]) = "Daily", MAX('Unpivot Daily'[Daily]),

    VALUES('Control Table'[Periodicity]) = "Weekly", MAX('Unpivot Weekly'[YearWeek]),

    BLANK())

This is the visualisation:

In case it raises an eyebrow, the reason I'm pulling data from 3 different tables for the same fields is because most of these columns are energy formulas made in SQL beforehand which will differ based on the aggregation type.

I believe I can make what I need with bookmarks (only read about those yesterday) but I was hoping to avoid creating 3 graphs and have them show/hide depending on the selection.

Thanks for taking the time!

Tom

[daxer-almighty · Accepted solution]

My question would be: how can I have my x-axis populate from 3 different columns depending on my 3-option periodicity selection?

Simply put: You can't. You can't switch between 3 different columns on one axis. An axis must source its values from one column only. ALWAYS. There's a video on YT from "The Guy in a Cube" that explains how to pull what you need off (but that requires a change to the model).

Also, looking at your model I tell you right away you should change it. It's not the way it should be. All the three tables should be consolidated into one since they are connected via 1-to-1. In reality, you should never have 1-to-1 relationships in your models.

[TomBLG]

Thanks Daxer. I ended up using the YearWeek field I wanted as a tooltip instead as it offered the same utility I was looking, however I'll be sure to attempt "The Guy in a Cube"'s solution for a future case.

As for the model, I'm aware I went down a rabbit hole there; I'm still lacking some know-how at this point.

I have 3 identical tables come in from SQL, same columns but aggregated differently. I needed a slicer based of the column name and the only way I managed to get it working was by creating a table (Tags) with all column names to use as a slicer and join it to the unpivoted version of my tables used with the measure below:

SWITCH( TRUE(),
VALUES('Control Table'[Periodicity]) = "Hourly", MAX('Unpivot Hourly'[Value]),
VALUES('Control Table'[Periodicity]) = "Daily", MAX('Unpivot Daily'[Value]),
VALUES('Control Table'[Periodicity]) = "Weekly", MAX('Unpivot Weekly'[Value]),
BLANK())

Initially I had 1-to-* relationships set between 'Unpivot Hourly' -- 'Unpivot Daily'  and 'Unpivot Hourly' -- 'Unpivot Weekly', but my values were all off so rather than managing to fix the root issue, I created a column with unique values based off [Attribute]&"_"&[TimeStamp] in each table and joined them all with 1:1 relationships. I think going back to this and making sure the initial 1-to-* relationships are done properly will leave the model making more sense.

I appreciate all the help, marking the reply as solution.

[daxer-almighty]

OK but what's the question? And what's the model? In Power Query you can massage all the tables into one table and have the axis switch granularity automatically based on the selected granularity. But still... I don't know what's the question and the particulars of your model.

[TomBLG]

Hi Daxer,

My question would be: how can I have my x-axis populate from 3 different columns depending on my 3-option periodicity selection? The columns I'd like to use for each selection are Hourly, Daily and YearWeek. Both Hourly and Daily are datetime timestamps with a date hierarchy and YearWeek is a 4-digit int.

I made a measure similar to the one I used to populate my graph values however dragging that measure onto the Axis data field did nothing, something is off.

The model would be this:

The idea here was to have a slicer with all 200+ energy meters to select from and populate the graph. For that I made a 1-column table named Tags with all the meter column names in the Hourly table (they're all identical tables aggregated differently).

I then couldn't find a way to have a measure read a different column based on my meter selection so I unpivotted the 3 tables, made a unique primary key made of [TimeStamp]&"_"&[Tag], connected them all with 1:1 relationships and created this measure for my Values field:

Periodicity Selection =

SWITCH( TRUE(),

    VALUES('Control Table'[Periodicity]) = "Hourly", MAX('Unpivot Hourly'[HourlyValue]),

    VALUES('Control Table'[Periodicity]) = "Daily", MAX('Unpivot Daily'[DailyValue]),

    VALUES('Control Table'[Periodicity]) = "Weekly", MAX('Unpivot Weekly'[WeeklyValue]),

    BLANK())

[HourlyValue], [DailyValue] and [WeeklyValue] are just the Attribute column from unpivoting renamed.

The main problem is in creating a similar measure for my graph's X axis.

Unfortunately I'm unable to share the .pbix as it has client data and I'm not fluent enough to turn all this into dummy data in a speedy manner for you. Let me know if there's insufficient info to go on.

I did manage to get YearWeek to show on the tooltip f Weekly is selected when mouseovering the graph, so I can run with that if the alternative is too troublesome without more details.

Appreciate all the help.