topic Re: Slicer in DAX Commands and Tips

Slicer

Anonymous — Sat, 16 Apr 2022 13:05:52 GMT

I am trying to set up a slicer which filters the week number across two data sets combined into one report.

I have orders put through counted by username and orders not put through counted by username.

when I go to filter by week it only filters one at a time.

does anyone know the soloution for this?

Re: Slicer

tamerj1 — Sat, 16 Apr 2022 14:15:47 GMT

Hi Anonymous

the week column from which table? What is the relationship between the two tables? Do you have a date table?

Re: Slicer

Anonymous — Sat, 16 Apr 2022 14:30:59 GMT

Hi,

so I have two duplicate datasets. One for "orders not put through" one for "orders put through".

On both of these tables there is a column for Week 1-52. The relationships I currently have are between user ID and Department.

currently, my slicer for week when applied only works for one data set and not the other. I've tried all sorts to try and resolve (including amending relationships) but nothing worked, I suspect it's fairly simple. But just cannot figure it out.

Re: Slicer

tamerj1 — Sat, 16 Apr 2022 14:39:39 GMT

Anonymous

Can share a screenshot of your visual? Also you did not answer one of the questions, the week column in the slicer belongs to which table?

Re: Slicer

Anonymous — Mon, 18 Apr 2022 07:30:57 GMT

Sorry about that. I have multiple kpi based tables E.G orders put through/not put through via department, and put through and not put through by individual Co-Worker, that can all be filtered by departmet (which works) and then can be filtered by week.

When i filter via week it will only filter one data set E.G. orders not put through and then orders put through will not have responded to the slicer. Both data sets have an existing week column in. I have tried to ammend the relationships between tables (as I thought this was the issue) but this is the error which i get.

Re: Slicer

tamerj1 — Mon, 18 Apr 2022 07:49:51 GMT

Anonymous
I understand your frustration and sorry for asking many questions. I know it is sometimes more difficult to express and explain a problem than solving.
The reason for receiving such error is that there is already a relationship between both tables. Can you please confirm which columns are involved in this relationship?
In general for such cases you have to have a Date table the filters both tables and then use this table in slicing and dicing in your visuals. Please share some sample data of the two tables and advise the code of both measure in order to simulate the situation to achieve a proper solution. Thank you.

Re: Slicer

Anonymous — Tue, 19 Apr 2022 08:49:44 GMT

Hi Anonymous ,

I created some data:

orders not put through：

orders put through：

You can try to create a calendar table, use the week of the calendar table as a slicer, and create a measure to mark it.

Here are the steps you

1. Create calculated table.

Table = CALENDAR(MIN('orders not put through'[date1]),MAX('orders put through'[date2]))

2. Create measure.

Flag = VAR _select=SELECTEDVALUE('Table'[WEEK]) return IF( WEEKNUM(MAX('orders not put through'[date1]),1)=_select || WEEKNUM(MAX('orders put through'[date2]),1)=_select,1,0)

3. Put [Flag] in the Filter of the two visual objects and set is =1.

4. Result:

Please click here for the pbix file

If I have misunderstood your meaning, please provide your pbix without privacy information and desired output.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly