topic Re: How to control table/matrix date columns with filter? in DAX Commands and Tips

How to control table/matrix date columns with filter?

Anonymous — Thu, 13 May 2021 02:25:03 GMT

Hi I have two tables from different data sources that look like this, with values under month columns.

Is it possible to set up a date/month filter to control the columns that show on both tables and compare the values from the different sources?

Cheers

Re: How to control table/matrix date columns with filter?

amitchandak — Thu, 13 May 2021 05:32:49 GMT

Anonymous , You need to create a common date table with date, month year , year columns and use date table as column in visual

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: How to control table/matrix date columns with filter?

Anonymous — Thu, 13 May 2021 09:27:54 GMT

Sorry buddy i don't get it. I want the date columns in my table to appear and disappear according to a filter. Is that possible?

I've been trying to merge the queries but with no success. While this won't solve my problem it might help.

Here's what i'd like the merge to give me:

It'd join the two tables aligning the matching columns. I known it might make more sense tweaking the raw data.

Just wondering if PowerBI can do this.

Cheers

Re: How to control table/matrix date columns with filter?

PaulOlding — Thu, 13 May 2021 10:05:38 GMT

I'd advise a bit of remodelling in power query so the data's easier for Power BI to work with.

First highlight all the month columns then click Unpivot Columns

The data will now look like this:

Add a Custom Column to have the source

Repeat the same steps for your second table.

Finally, use 'Append Queries' to get a single table with all the data.

Now you can use a matrix, or any other visual that makes sense, to visualise the data and have a slicer on Attribute (you'll probably want to rename that column in Power Query) to filter just selected months

Re: How to control table/matrix date columns with filter?

Anonymous — Thu, 13 May 2021 23:53:24 GMT

Thank you Paul what a great solution. I'll just need some more research as the Source columns in both tables are erroring due to a cyclic reference. Not sure why....

Also the Source was just named 'Table' in both queries leaving me no way to identify them. Is there a way of renaming them manually?

Re: How to control table/matrix date columns with filter?

Anonymous — Fri, 14 May 2021 02:08:12 GMT

All sorted it works perfectly. Thank you.