Slicer to include all part numbers from 3 datasets

[Anonymous]

Hi,

I have 3 datasets, with shared header Part Number.

Dataset 1 has Part Number, Qty Damaged, Reason Code and might include parts A & B

Dataset 2 has Part Number, Qty Reservered, Customer ID and might include parts C & D

Dataset 3 has Part Number, Qty Due this Week, Delivery Date/Time and might include parts A, E & F

How can I create one overall table, or a dashboard with a table for each dataset, where I can use a slicer to search everything? Example the slicer would have all parts A-F and if I search Part A I would see there is some damged, none reserved and some due this week?

The slicer doesn't seem to capture all Part Numbers for me, I'm sure it's my relationships but I can't figure it out.. 

Thank you in advance!!!

[mwegener]

Hi @Anonymous ,

was your problem solved?

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Dataset1:

Dataset2:

Dataset3:

Table(a calculated table):

Table = 
DISTINCT(
    UNION(
        DISTINCT(Dataset1[Part Number]),
        DISTINCT(Dataset2[Part Number]),
        DISTINCT(Dataset3[Part Number])
    )
)

There is no relationship between tables. You may create measures as below.

Qty Damaged measure = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Qty Damaged",
    var _partnumber = [Part Number]
    return
    CALCULATE(
        SUM(Dataset1[Qty Damaged]),
        FILTER(
            ALL(Dataset1),
            Dataset1[Part Number]=_partnumber
        )
    )
)
return
SUMX(
    tab,
    [Qty Damaged]
)

Reason Code = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Reason Code",
    var _partnumber = [Part Number]
    return
    CONCATENATEX(
        FILTER(
            ALL(Dataset1),
            Dataset1[Part Number]=_partnumber
        ),
        [Reason Code],
        ","
    )
)
return
MAXX(
    tab,
    [Reason Code]
)

Qty Reservered measure = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Qty Reservered",
    var _partnumber = [Part Number]
    return
    CALCULATE(
        SUM(Dataset2[Qty Reservered]),
        FILTER(
            ALL(Dataset2),
            Dataset2[Part Number]=_partnumber
        )
    )
)
return
SUMX(
    tab,
    [Qty Reservered]
)

Customer ID = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Custom ID",
    var _partnumber = [Part Number]
    return
    CONCATENATEX(
        FILTER(
            ALL(Dataset2),
            Dataset2[Part Number]=_partnumber
        ),
        [Customer ID],
        ","
    )
)
return
MAXX(
    tab,
    [Custom ID]
)

Qty Due this Week measure = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Qty Due this Week",
    var _partnumber = [Part Number]
    return
    CALCULATE(
        SUM(Dataset3[Qty Due this Week]),
        FILTER(
            ALL(Dataset3),
            Dataset3[Part Number]=_partnumber
        )
    )
)
return
SUMX(
    tab,
    [Qty Due this Week]
)

Delivery Date = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Part Number]
    ),
    "Delivery Date",
    var _partnumber = [Part Number]
    return
    CONCATENATEX(
        FILTER(
            ALL(Dataset3),
            Dataset3[Part Number]=_partnumber
        ),
        [Delivery Date/Time],
        ","
    )
)
return
MAXX(
    tab,
    [Delivery Date]
)

Finally you may use 'Part Number' from 'Table' to filter the result.

Best Regards

Allan

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

[amitchandak]

@Anonymous , You can create a part number table like

distinct(union(all(Dataset1[Part Number]),all(Dataset2[Part Number]),all(Dataset2[Part Number])))

in the above give cross reference issue
summarize(union(all(Dataset1[Part Number]),all(Dataset2[Part Number]),all(Dataset2[Part Number])),[Part Number])

[Anonymous]

Hi @amitchandak 

Thank you so much for helping me, I did not know you could create a table like this.

I have tried both of your suggestions and now I have a table with all part numbers, so I can create a slicer with all part numbers.

But this slicer does not update the part displayed in my original tables - there is no relationship between the new table  and my original tables. If I try to create one I get a circular dependency error?

I'm sorry I don't know how to share a screenshot here...

[amitchandak]

@Anonymous , This circular dependency is new release issue. I use the same in past and it use to work, I will log an issue. 

summarize(union(distinct(Dataset1[Part Number]),distinct(Dataset2[Part Number]),distinct(Dataset2[Part Number])),[Part Number])

and join with all three tables one directional 1 to M join 

[Pragati11]

HI @Anonymous ,

First thing - Share a scressnhot of your data-model with relationships here. This will help in understanding on what column you are creating the relationship.

Second thing - Out of these 3 tables, is there any table which has all the values against PARTS (A to F) to be used as a slicer?

Thanks,

Pragati