how to filter in query

[kaileena]

Hello,

I paste here a sample of my data (Measurement table with all columns):

Here is my slicer which contains a subset of Entity (Entity Reference Table column Entity PK).

(Measurement Table references Entity Table of course)

The problem is that: i am forced to have a subset of entities in the slicer which actually represent the parent entities (Payer, pib...). They are parent entities for all the other entities which are also present in the Measurement table (i.e. Payer entity is parent for archery, karate, surfing ). In other words: in my Measurement Table in the column called Entity I have all the entities (parent plus children) whereas in the slicer I have to filter only on the parent entities. 

The relation parent-child can be seen by looking at the Cadence column from the Measurement Table.

Now I have to calculate the Story point measure for the child entities (since i do not already have an aggregate at parent entity level). I have so far:

But it does not work....

[v-jianboli-msft · Accepted solution]

Hi @kaileena ,

Based on your description, I have modified yor measure, please try:

Value for Story Points Delivered = 
SWITCH(
    SELECTEDVALUE('Entity Reference'[Entity (PK)]),
    "Payer",
    CALCULATE(
        SUM(Measurement[Value]),
        FILTER(ALL(Measurement),
        'Measurement'[Type] in {"Story Points Delivered"}&&
        CONTAINSSTRING('Measurement'[Cadence],"PAYER") = TRUE()
    )),0)

Final output:

If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.