Hello,
Why does PAW think this is an invalid MDX ?
{Distinct( {[Wim_].[Rolling 12 periods].[Rolling 12 periods Jan 2021^202101]}, {[Wim_].[Rolling 12 periods].[Rolling 12 periods Feb 2021^202101]} )}
The dimension is called "Wim_"
The hierarchy is called "Rolling 12 periods"
The same element "202101" rolls up into "Rolling 12 periods Jan 2021" and "Rolling 12 periods Feb 2021"
Without Distinct( ) it correctly returns 2 elements. With Distinct() I get "Invalid MDX expression".
I would expect to have PAW return the set with the same 2 elements (same name, different MUN).
------------------------------
Wim Gielis
------------------------------
Original Message:
Sent: Wed March 02, 2022 11:59 AM
From: Ryan Clapp
Subject: Removing duplicate elements when pasting in the Set editor in PAX
Keep in mind though DISTINCT({MDX Statement}) is not the same as starting with TM1SubsSetAll. Distinct is looking for unique MUNs (Member Unique Name).
[Product].[Product].[New Products^A] is a different member than [Product].[Product].[Products On Sale^A]
------------------------------
Ryan Clapp
Original Message:
Sent: Tue March 01, 2022 03:30 AM
From: Hubert Heijkers
Subject: Removing duplicate elements when pasting in the Set editor in PAX
Hi Luiz, Scott,
If you are switching to MDX already, as Scott suggests, you can simply put a DISTINCT( {Rest of the existing MDX code} ) around it which removes any duplicates from the given set while retaining the first instance of each element from that original set.
------------------------------
Hubert Heijkers
Original Message:
Sent: Mon February 28, 2022 12:27 PM
From: Scott Brown
Subject: Removing duplicate elements when pasting in the Set editor in PAX
Hi,
By default the subset editor does not remove duplicates. In perspectives, if you use the set editor and choose hierarchy sort, it removes duplicates as long as the element does not show up in the hierarchy more than once.
In PAW/PAX subset editor you need to do a work around. If you switch to MDX mode, and add the following it should get rid of duplicates:
INTERSECT( [dimension name].MEMBERS, {Rest of the existing MDX code } )
Another work around is to paste the elements into a new tab in excel and choose the remove duplicates. Then copy it again and paste into the subset editor.
------------------------------
Scott Brown
Original Message:
Sent: Mon February 28, 2022 12:12 AM
From: Luiz gustavo Ribeiro
Subject: Removing duplicate elements when pasting in the Set editor in PAX
Hello,
When copying text from an excel sheet/quick report and pasting in the set editor we get duplicated elements if in the copied selection there are more than one occurrence of the same element. Is there an way to removing the duplicates in the set editor and keeping only one instance of each unique element?
I could swear that this was a normal behavior before but now users are reporting that pasting in the set editor is generating a lot of duplicates of the same element. Thanks.
------------------------------
Luiz gustavo Ribeiro
------------------------------
#PlanningAnalyticswithWatson