locked
Existing working with only one member selected RRS feed

  • Question

  • Hi,

    As the title tells, I got a big problem.

    The following calculation works perfectly by itself:

    MIN(EXISTING([DimensionCustomers].[Customer].[Customer].Members,[Measures].[MyMeasure]))

    But when I report this calculation in Power BI, and I use a slicer to filter customers, it only works if every member is selected or if just one member is selected, but it doesn't work at all for more than members as selected: in that case it shows me the value as if all members are selected.

    is there a way to solve this?

    Thanks

    Wednesday, July 29, 2020 4:31 PM

Answers

  • @Alexei so basically what you are suggesting is to create a dynamic set (filtered by existing) and use that in the calculation member formula?

    This is correct, however if I remember correctly I don't think you need to use EXISTING in the dynamic set. I think it will get re-evaluated in the current filter context anyway (although it's been years since I've done this so you should double check)

    So this would make your set and measure something like the following:

    CREATE DYNAMIC SET CURRENTCUBE.CustomerSet AS
    [DimensionCustomers].[Customer].[Customer].Members;

    CREATE MEMBER Measures.[My Measure] AS MIN([CustomerSet] ,[Measures].[MyMeasure]));


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by BossCo2 Thursday, July 30, 2020 11:08 AM
    Thursday, July 30, 2020 9:55 AM

All replies

  • Hi,

    How have you specified the "more than one member" set? How did you pick them up?

    Could you clear you issue with more detail or screenshot?

    I guess you are trying to find the min measure value for selected customer ?

    Check the doc of Existing  see if you miss something.



    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 30, 2020 5:46 AM
  • It seems that, when you select several members in the slicer, Power BI generates an MDX query that contains a subselect. Should be easy to verify with a trace against the cube. The only known solution to the subselect problem is a dynamic set

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, July 30, 2020 7:22 AM
  • Hi! Thanks for your answers.

    @Lukas, I'm looking for the min measure value for the selected customer or customers, chosen with a slicer filter in Power BI. It's a simple visual filter with all the customers where you can select one, more than one or all the customers. Now if I select only one customer than it works properly and I get the min (and only) measure value for the selected one. If I select more than one, I get the min measure value for all the customers, regardless of what I selected as if I had selected all customers.

    @Alexei so basically what you are suggesting is to create a dynamic set (filtered by existing) and use that in the calculation member formula?

    Thursday, July 30, 2020 8:32 AM
  • @Alexei so basically what you are suggesting is to create a dynamic set (filtered by existing) and use that in the calculation member formula?

    This is correct, however if I remember correctly I don't think you need to use EXISTING in the dynamic set. I think it will get re-evaluated in the current filter context anyway (although it's been years since I've done this so you should double check)

    So this would make your set and measure something like the following:

    CREATE DYNAMIC SET CURRENTCUBE.CustomerSet AS
    [DimensionCustomers].[Customer].[Customer].Members;

    CREATE MEMBER Measures.[My Measure] AS MIN([CustomerSet] ,[Measures].[MyMeasure]));


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by BossCo2 Thursday, July 30, 2020 11:08 AM
    Thursday, July 30, 2020 9:55 AM
  • Working! Thanks a lot

    Antonio

    Thursday, July 30, 2020 11:08 AM