cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Combine 2 facts with 1 common dimension

Hi folks,

 

We have these kind of data:

 

Fact 1:
_clientid type  from             to
1            A      2015-01-01  2018-01-01
1            B      2014-07-01  2018-01-01
and so on

 

Ps;from and to is additional info, not anything to be important in filtering....


Fact 2:
_clientid Status
1            Done
....
4            Done
4            Not Done

 

Clients

_clientid     name

1                Aaaaa

2                Bbbbb

And so on 

 

Our model is really simple:

 

1.png

 

We now want/expect these output when we filter on _clientid = 1:

 

Name              Type             Status

Aaaaaa            A                  Done

Aaaaaa            B                  Done

 

But we get:

 2.png

 

 

When we create something with on both sides 1 record per _clientid everything works fine, also all the examples that we find are talking about creating a unique key and so on, but NOTHING except the _clientid is available in both tables.....

 

So; is it possible what we want?

 

 

1 ACCEPTED SOLUTION


@MiKeZZa wrote:

Hmmm.... That's not what we are looking for.

 

I really need a cross join. Sad that this is not possible.


@MiKeZZa

A cross join is possible, not in the visual, but via a calculated table. Create a calculated table as below and then link it to the client table.

 

Table2 =
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS ( Fact1, "clientid", Fact1[_clientid], "type", Fact1[type] ),
        Fact2
    ),
    [clientid] = Fact2[_clientid]
)

View solution in original post

6 REPLIES 6
Microsoft
Microsoft


@MiKeZZa wrote:

Hi folks,

 

We have these kind of data:

 

Fact 1:
_clientid type  from             to
1            A      2015-01-01  2018-01-01
1            B      2014-07-01  2018-01-01
and so on

 

Ps;from and to is additional info, not anything to be important in filtering....


Fact 2:
_clientid Status
1            Done
....
4            Done
4            Not Done

 

Clients

_clientid     name

1                Aaaaa

2                Bbbbb

And so on 

 

Our model is really simple:

 

1.png

 

We now want/expect these output when we filter on _clientid = 1:

 

Name              Type             Status

Aaaaaa            A                  Done

Aaaaaa            B                  Done

 

But we get:

 2.png

 

 

When we create something with on both sides 1 record per _clientid everything works fine, also all the examples that we find are talking about creating a unique key and so on, but NOTHING except the _clientid is available in both tables.....

 

So; is it possible what we want?

 

 


@MiKeZZa

Why there're two status in Fact2 for client 4? You can only get the expected output when the relationship between Client and Fact2 is one to one, instead of one to many.

No, in fact it is one to many.

 

So when you have these line:

 

Fact 1 - Dim - Fact 2

 

The cardinality is: Many - 1 - Many

 

What I want is a kind of cross join (when you think SQL style)


@MiKeZZa wrote:

No, in fact it is one to many.

 

So when you have these line:

 

Fact 1 - Dim - Fact 2

 

The cardinality is: Many - 1 - Many

 

What I want is a kind of cross join (when you think SQL style)


@MiKeZZa

Then the expected output is not reasonable. Fact1 doesn't have unique matching status in Fact2. Cartesian Product is expected instead of the output in you post. So try to create a measure as

Measure   = CONCATENATEX(Fact2,Fact2[status],",")

 

Hmmm.... That's not what we are looking for.

 

I really need a cross join. Sad that this is not possible.


@MiKeZZa wrote:

Hmmm.... That's not what we are looking for.

 

I really need a cross join. Sad that this is not possible.


@MiKeZZa

A cross join is possible, not in the visual, but via a calculated table. Create a calculated table as below and then link it to the client table.

 

Table2 =
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS ( Fact1, "clientid", Fact1[_clientid], "type", Fact1[type] ),
        Fact2
    ),
    [clientid] = Fact2[_clientid]
)

View solution in original post

Yes, this is what we are looking for Smiley Very Happy

 

I'll give it a try with a larger dataset at a later moment, but now it works very fine!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors