Filter and summarize table by multiple related conditions

Question

Let's say I have 3 tables, Table 1 is the parent information for table 2 Table 2 is the unpivoted information describing table 1 Table 3 is the filter condition

Example: Table1:

ID	Name
1	T1
2	T2
3	T3

Table2 (Table1[ID] <-> Table2[Parent ID]):

ID	Parent ID	Name	Value
1	1	Ca	1
2	1	Cb	1
3	2	Ca	1
4	2	Cb	1
5	2	Cc	1
6	2	Cd	1
7	3	Ca	1
8	3	Cb	0

Table3:

ID	Group	Name	Min Value	Max Value
1	G1	Ca	1	2
2	G1	Cb	1	2
3	G2	Ca	1	null
4	G2	Cb	1	null
5	G2	Cc	1	1

I would like to display in table for how many Parent meet the criteria, but I not sure how to link Table 2 and Table 3. Or does it no need to link, can just using measure?

Sample outcome:

Group	Match Pattern
G1	2
G2	1

Explanation: So from the 3 tables, I should get the output where there are 2 parent satisfied the condition of G1 from Table 3, while only 1 parent satisfied the condition of G2. For G1, Ca and Cb must be in between 1 and 2 inclusively. T1 and T2 fulfill the criteria, while T3 has Cb = 0. Thus answer is 2. For G2, Ca and Cb must be >= 1, while Cc must be equal 1. Only T2 fulfill the criteria, T1 does not meet because missing Cc, T3 missing Cc and Cb = 0. Thus answer is 1.

I'm pretty beginner to this, so I might not try much. I don't think linking the Name in Table 3 and Name in Table 2 is right, so I didn't try that. But I tried to create measure with COUNTX like below,

COUNTX(
    FILTER(
        Table2
        ,Table2[Name]=Table3[Name] 
          && 
          Table2[Value]>=Table3[Min Value] 
          && 
          Table2[Value]<=Table3[Max Value]
    )
   ,Table2[Parent ID]
)

, but all total match pattern I get is 0.

I cant get the point from your explanation. So, please, make it more simple for understanding. may be step by step with some math — Mik
– Mik, Commented Dec 28, 2022 at 10:01

Peter · Accepted Answer · 2022-12-28 12:35:56Z

1

Create a new Observations table from Table2 with an added Parent Name from Table1

Observations = 
SELECTCOLUMNS(
    Table2,
    "ParentName", RELATED('Table1'[Name]),
    "ObsParameter", Table2[Name],
    "Value", Table2[Value]
)

Create a new Conditions table from Table3 with a 999 Max Value instead of null

Conditions = 
SELECTCOLUMNS(
    Table3,
    "Group", Table3[Group],
    "CondParameter", Table3[Name],
    "Min", Table3[Min Value],
    "Max", IF(ISBLANK(Table3[Max Value]), 999, Table3[Max Value])
)

Crossjoin Conditions with Observation, filter matches and group by Group and Parent Name

Match = 
GROUPBY(
    FILTER(
        CROSSJOIN(Conditions, Observations),
        [CondParameter] = [ObsParameter]
        && [Min] <= [Value]
        && [Value] <= [Max] = TRUE()
    ),
    [Group],
    [ParentName],
    "Count", COUNTX(CURRENTGROUP(), [ParentName])
)

Summarize Table3 as ConditionCount to get the number of conditions per group and create a relation between 'ConditionCount'[Group] and 'Match'[Group]

ConditionCount = 
SUMMARIZE(
    Table3,
    Table3[Group],
    "NumConditions", COUNT(Table3[ID])
)

Filter on Match[Count] = NumConditions and group again to count the parents

Result = 
GROUPBY(
    FILTER(
        Match,
        Match[Count] = RELATED(ConditionCount[NumConditions])
    ),
    [Group],
    "Match Pattern", COUNTX(CURRENTGROUP(),[ParentName])
)

edited Dec 28, 2022 at 12:35

answered Dec 28, 2022 at 10:25

Peter

12.7k2 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manny Riveira Over a year ago

Hi @Peter. Sorry for responding late. It took some time for me to try it out, but it really helps me to understand how to use power bi. Thanks a lot!

Collectives™ on Stack Overflow

Filter and summarize table by multiple related conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related