Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiKeZZa
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
Eric_Zhang
Employee
Employee


@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]
)

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.