Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
We now want/expect these output when we filter on _clientid = 1:
Name Type Status
Aaaaaa A Done
Aaaaaa B Done
But we get:
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?
Solved! Go to Solution.
@MiKeZZa wrote:
Hmmm.... That's not what we are looking for.
I really need a cross join. Sad that this is not possible.
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] )
@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:
We now want/expect these output when we filter on _clientid = 1:
Name Type Status
Aaaaaa A Done
Aaaaaa B Done
But we get:
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?
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)
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.
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
I'll give it a try with a larger dataset at a later moment, but now it works very fine!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |