Forum Discussion

XELANAMYT's avatar
XELANAMYT
Frequent Visitor
11 months ago
Solved

DAX Measure to aggregate parent table values which preserving filter on child table

I'm trying to create a measure which will aggregate the data of a parent-child relationship while preserving the filters applied via other dimension tables on both tables.

 

This is a simplified example of my data model.

Measure1 = 
    CALCULATE(
        AVERAGE('Child'[ValA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name])
    )

The above measure works fine, the values changes when the filters on the dimension tables are hooked up to slicer visuals.

Measure2 = 
    CALCULATE(
        AVERAGE('Parent'[ValPA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name])
    )

The second measure doesn't respect change filters applied via the 'Users'Name column, but all the other filters work fine.

I'm sure I'm missing something fairly simple here, but I've gone code blind and going around in circles, so any help would be appreciated.

Need Help
  • MFelix's avatar
    MFelix
    11 months ago

    Hi XELANAMYT ,

     

    There is no relationship between the parent and the Users table so there is no impact on the filter you are doing in the Users table.

     

    Since you have a relationship of one to many from the users to the child, and the child is also on the many side of the relationship you are not getting the values filtered back to the parent.

     

    Try something like this:

    Measure2 = 
    CALCULATE(
        AVERAGE('Parent'[ValPA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name]),
        'Parent'[ID] in VALUES('Child'[ParentID])
    )

     

    Another option can also be:

    Measure2 = 
    CALCULATE(
        AVERAGE('Parent'[ValPA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name]),
        CROSSFILTER('Parent'[ID],'Child'[ParentID], Both)
    )

    This one forces the cross filter between the tables to go either way from parent to child and from child to parent.

     

    Use the columns that are being used on the relationship between parents and child.

     

    The first measure is working properly because the Parent Table is a dimension table reagarding the child so every filter you apply to the parent is passed to the child.

     

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    11 months ago

    Hi XELANAMYT ,

     

    There is no relationship between the parent and the Users table so there is no impact on the filter you are doing in the Users table.

     

    Since you have a relationship of one to many from the users to the child, and the child is also on the many side of the relationship you are not getting the values filtered back to the parent.

     

    Try something like this:

    Measure2 = 
    CALCULATE(
        AVERAGE('Parent'[ValPA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name]),
        'Parent'[ID] in VALUES('Child'[ParentID])
    )

     

    Another option can also be:

    Measure2 = 
    CALCULATE(
        AVERAGE('Parent'[ValPA]),
        ALLEXCEPT('Child', 'Zones'[Name], 'Categories'[Name], 'States'[Name], 'Users'[Name]),
        CROSSFILTER('Parent'[ID],'Child'[ParentID], Both)
    )

    This one forces the cross filter between the tables to go either way from parent to child and from child to parent.

     

    Use the columns that are being used on the relationship between parents and child.

     

    The first measure is working properly because the Parent Table is a dimension table reagarding the child so every filter you apply to the parent is passed to the child.

     

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Icon for Super User rankSuper User
    11 months ago

    Hi XELANAMYT 

    You can try the below dax ?

    As the every table is directly connected to parent with 1 - *

    and you want a user table also filter your visuals then use the below dax 

    Measure2 =
    CALCULATE(
    AVERAGE('Parent'[ValPA]),
    TREATAS(
    VALUES('Child'[ID]),
    'Parent'[ID]
    )
    )

     

     

     

    The reason why in your mesures the user table is not filter is because the user table is connect to child and child is connect to parent but the direct of flow is from parent to child table.

    If this answers your questions, kindly accept it as a solution and give kudos

    • mdaatifraza5556's avatar
      mdaatifraza5556
      Icon for Super User rankSuper User
      11 months ago

      Hi XELANAMYT 


      Or you can also try the below dax

      Measure2 =
      CALCULATE(
      AVERAGE('Parent'[ValPA]),

      TREATAS(
      VALUES('Child'[ID]),
      'Parent'[ID]
      ),


      REMOVEFILTERS('Parent'),

      KEEPFILTERS(VALUES('Zones'[Name])),
      KEEPFILTERS(VALUES('States'[Name])),
      KEEPFILTERS(VALUES('Categories'[Name]))
      )



      If this answers your questions, kindly accept it as a solution and give kudos.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi XELANAMYT ,
    Thanks for raising this in Microsoft Fabric Community.

    MFelix  and mdaatifraza5556  have shared valid approaches that ensure the Parent table aggregates respect filters applied through the Child, including from Users.

     

    Thanks MFelix and mdaatifraza5556  for your inputs here.

    XELANAMYT , could you confirm if either of these worked in your case? If not, please share more details so we can look into it further.

     

    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi XELANAMYT ,
    We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
    Thank you.