Battling to get a cubeset to sort correctly

[Darryll_B1986]

Good afternoon everyone

I am new here & this is my first post.

I have been searching the web & have not managed to find a solution to my problem.

Using the following formula, I am able to get the correct list of suppliers, but I am wanting to sort the list based on Units for the whole period (12 months). 

=CUBESET("ThisWorkbookDataModel","Except(Filter(EXISTS([OCR_VW_RPT_OCR].[PARENT_SUPPLIER_NAME].MEMBERS,[OCR_VW_RPT_OCR].[RETAINED_MONTH].[01 Aug 2024].lag(11):[OCR_VW_RPT_OCR].[RETAINED_MONTH].[01 Aug 2024]),[Measures].[EACT UNITS]>0),[OCR_VW_RPT_OCR].[PARENT_SUPPLIER_NAME].[All])","PARENTSUPPLIERNAME",2,"[Measures].[EACT UNITS]")

Can anyone spot the syntax error or is there maybe a different way/method of doing this? My generated list matches my manually created list perfectly, but the sort does not.

Any help on this would be highly appreciated.

Thanks,

Darryll

[ahadkarimi]

Hi @Darryll_B1986, give this a try, and if you encounter any issues, let me know.

=CUBESET("ThisWorkbookDataModel", 
    "Except(
        Filter(
            EXISTS([OCR_VW_RPT_OCR].[PARENT_SUPPLIER_NAME].MEMBERS,
            [OCR_VW_RPT_OCR].[RETAINED_MONTH].[01 Aug 2024].lag(11):[OCR_VW_RPT_OCR].[RETAINED_MONTH].[01 Aug 2024]), 
            [Measures].[EACT UNITS]>0),
            [OCR_VW_RPT_OCR].[PARENT_SUPPLIER_NAME].[All]
        ), 
        [Measures].[EACT UNITS]
    )", 
    "PARENTSUPPLIERNAME", 
    2, 
    "[Measures].[EACT UNITS]"
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

[Darryll_B1986]

Thank you for the reply @ahadkarimi , I get the following error when I use the formula provided, I think that there might be a missing " somewhere:

I cannot spot anything wrong with the formula though.

Thanks.

[Anonymous]

Hi @Darryll_B1986 

Thanks for the reply from ahadkarimi .

@Darryll_B1986 , if the problem persists, could you please provide some sample data and desired results based on the sample data so we can better help you? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots. Please remove any sensitive data in advance. 

Best Regards,
Yulia Xu