Create arbitrary link between visuals

[Anonymous]

Lets say we have a data set which has "year" among other dimensions (not the datetime - year as integer), and visual built upon this dataset. We also have a calendarauto() based slicer which allows us to select year and filter displayed data. Visual's dataset is NOT linked to calendarauto() table.

Is there any way to create a link between the slicer and this visual, so selecting year on the slicer would filter the data?

[Pragati11]

Hi @Anonymous ,

You can create a YEAR column in Calendar Date table. Then use this column to establish a relationship between you data-set and Date table.

This will make slicers work the way you are looking for.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Relationships are out of question because of the dreaded "circular relationship" error. We just cannot create them when needed.

[Pragati11]

Hi @Anonymous ,

Is there an existing relationship betwwen your Date table and data-table? Or are these individually connected to any other table?

Can you try pasting a screenshot of the data model with relationships in Power BI here?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

There is no relationship (within the model).

[Pragati11]

Hi @Anonymous ,

Can you paste your sample data here, along with the datatypes the columns have in both tables?

Thanks,

Pragati

[Anonymous]

I cannot post the sample data because, if I only recreate 2 tables the answer would be "create a relationship". But as I've already mentioned above, creating relationship is out of question because this would create circular relationships.

The question is rather simple. There is a CALENDARAUTO() - based slicer that lets you select years. There is also a table that has a "year" column (integer) and other data, and visual built using this table. The goal is: slicer should filter the visual.

[v-gizhi-msft]

Hi,

Please try this measure:

Measure = IF(MAX('Table'[Year])=SELECTEDVALUE('Calendar'[Year]),1,0)

Then apply this measure to the visuals by setting measure=1.

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

I cannot even reproduce this configuration in actual file. In your example, CALENDARAUTO-based table has no year/quarter/month fields - somehow, because in real file it is impossible to remove fields or edit CALENDARAUTO() hierarchy in any way.

How did you delete hierarchy from calendarauto?

[v-gizhi-msft]

Hi,

It is because i created relationship between them which will break the calendar hierarchy.

Please check my attached file again.

Best Regards,

Giotto