topic Re: Battling to get a cubeset to sort correctly in DAX Commands and Tips

Battling to get a cubeset to sort correctly

Darryll_B1986 — Mon, 23 Sep 2024 14:29:10 GMT

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

Re: Battling to get a cubeset to sort correctly

ahadkarimi — Mon, 23 Sep 2024 15:04:49 GMT

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!

Re: Battling to get a cubeset to sort correctly

Darryll_B1986 — Mon, 23 Sep 2024 16:31:14 GMT

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.

Re: Battling to get a cubeset to sort correctly

Anonymous — Tue, 24 Sep 2024 05:33:17 GMT

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