How to use 'wildcards' across data model connections

[pjmorris]

This problem follows on from a previous discussion (Use of Multiple 'USERELATIONSHIP' in a single measure) (Rory, thank you).

The problem is that I wish to avoid creating a calculated field to link the dimension table and the fact table. This is because, based on my calculations, the dimension table would have something in excess of 4.5M rows - and it slow enough at the moment with 120K lines.

I have created an example spreadsheet, but don't know how to attach it!. The relevant parts are below:

1. The Fact Table:

DID	Company	WBS	Value
E205	NOV	505	1
E205	NOV	505	2
E205	NOV	505	4
E205	NOV	505	8
ILS018	NOV	505	16
ILS018	NOV	100	32
ILS018	ALF	100	48
ILS018	ALF	505	64
ENG207	ALF	505	80
ENG207	ALF	505	96
ENG207	ALF	100	112
ENG207	ALF	100	128

2. The Dimension Table: The key thing here is to note that two of the fields are blank (unspecified) and are highlighted in pink.

Group	DID	Company	WBS
Gandalf	E205	NOV	505
Morris	ILS018		100
Buggins	ENG207	ALF

3. Required Output is:

Expected Results	Results
Gandalf	15
Morris	80
Buggins	416

4. Data Model. Using Powerquery to create relevant intermediary tables from the Fact Table:

5. The Measure defined to achieve the results and the resulting output:

=CALCULATE(SUM(qryTable1[Value]), qryTable2)

Row Labels	sums
Abbas	15
Merritt
Morris
Grand Total	15

Problem:

It would appear that in applying the filters across the 3 intermediary tables (needed to handle the many-to-many relationships between the Fact and Dimension tables), where no data is provided in the pink cells this is being interpreted literally. What I would like it to do is simply ignore that filter condition and in effect apply a wildcard or 'any' criteria.

But I don't know how to do so.

Any ideas very gratefully received.

Regards

Peter

 

[JGordon11]

let
    Fact = Excel.CurrentWorkbook(){[Name="Fact"]}[Content],
    tbl1 = Table.Group(Fact, {"DID","WBS"}, {{"Results", each List.Sum([Value]), type number}}),
    Dim = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Dimension"]}[Content],{"Group", "DID","WBS"}),
    tbl2 = Table.NestedJoin(tbl1, "DID", Dim, "DID", "All"),
    tbl3 = Table.ExpandTableColumn(tbl2, "All", {"Group","WBS"}, {"Group","WBS1"}),
    tbl4 = Table.SelectRows(tbl3, each [WBS1] = null or [WBS]= [WBS1]),
    Result = Table.Group(tbl4, {"Group"}, {{"Results", each List.Sum([Results]), type number}})
in
    Result

Book2
	A	B	C	D	E	F	G	H
1	DID	Company	WBS	Value		Group	Results
2	E205	NOV	505	1		Gandalf	15
3	E205	NOV	505	2		Morris	80
4	E205	NOV	505	4		Buggins	416
5	E205	NOV	505	8
6	ILS018	NOV	505	16
7	ILS018	NOV	100	32
8	ILS018	ALF	100	48
9	ILS018	ALF	505	64
10	ENG207	ALF	505	80
11	ENG207	ALF	505	96
12	ENG207	ALF	100	112
13	ENG207	ALF	100	128
14
15
16	Group	DID	Company	WBS
17	Gandalf	E205	NOV	505
18	Morris	ILS018		100
19	Buggins	ENG207	ALF
20
Sheet1

 

[pjmorris]

let
    Fact = Excel.CurrentWorkbook(){[Name="Fact"]}[Content],
    tbl1 = Table.Group(Fact, {"DID","WBS"}, {{"Results", each List.Sum([Value]), type number}}),
    Dim = Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Dimension"]}[Content],{"Group", "DID","WBS"}),
    tbl2 = Table.NestedJoin(tbl1, "DID", Dim, "DID", "All"),
    tbl3 = Table.ExpandTableColumn(tbl2, "All", {"Group","WBS"}, {"Group","WBS1"}),
    tbl4 = Table.SelectRows(tbl3, each [WBS1] = null or [WBS]= [WBS1]),
    Result = Table.Group(tbl4, {"Group"}, {{"Results", each List.Sum([Results]), type number}})
in
    Result

Book2
	A	B	C	D	E	F	G	H
1	DID	Company	WBS	Value		Group	Results
2	E205	NOV	505	1		Gandalf	15
3	E205	NOV	505	2		Morris	80
4	E205	NOV	505	4		Buggins	416
5	E205	NOV	505	8
6	ILS018	NOV	505	16
7	ILS018	NOV	100	32
8	ILS018	ALF	100	48
9	ILS018	ALF	505	64
10	ENG207	ALF	505	80
11	ENG207	ALF	505	96
12	ENG207	ALF	100	112
13	ENG207	ALF	100	128
14
15
16	Group	DID	Company	WBS
17	Gandalf	E205	NOV	505
18	Morris	ILS018		100
19	Buggins	ENG207	ALF
20
Sheet1

Hi, firstly apologies for being so slow to reply.

This needs to be a Powerpivot solution rather than Powerquery as its the dynamic part of the data model. I'm currently now a multilayered data model (snowflake style), but other work problems are halting progress. If I can't get that to work I'll obviously give this approach a try.

Thank you for offering.

Regards

Peter