March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello all!
First I'd like to thank all who read this and intend to help, you make this comunnity awesome!
I'm trying to link 2 tables, a FACT and a DIMENSIONS one, but this latter doesn't have unique values. Let me try to exemplify:
So, I would like to get something like this... If the ticket matches the 2 tables AND the 'DIM a'=10
The result should be this:
Do anyone knows how to do that in DAX? Tried the VLOOKUP formula but didn't succeeded.
Thanks in advance
Solved! Go to Solution.
Hi, using a calculated column And DimA is 10
When DimA-10 = LOOKUPVALUE ( Table1[Dim B], Table1[Ticket], Tickets[Ticket]; Table1[Dim A], 10 )
Using a measure:
Measure-When DimA-10 = IF ( HASONEVALUE ( Table1[Ticket] ), LOOKUPVALUE ( Table1[Dim B], Table1[Ticket], VALUES ( Tickets[Ticket] ), Table1[Dim A], 10 ) )
let me know if works.
Victor
@Tulio_DL,
Create the calculated column in TABLE A.
When DimA-10 = LOOKUPVALUE ( 'TABLE B'[DIM b], 'TABLE B'[Ticket], 'TABLE A'[Ticket], 'TABLE B'[DIM a], 10 )
Also you can change the second formula of Vvelarde to the following:
Measure-When DimA-10 = IF ( HASONEVALUE ( 'TABLE B'[Ticket] ), LOOKUPVALUE ( 'TABLE B'[DIM b], 'TABLE B'[Ticket], VALUES ( 'TABLE A'[Ticket] ), 'TABLE B'[DIM a], 10 ) )
Regards,
Hi, using a calculated column And DimA is 10
When DimA-10 = LOOKUPVALUE ( Table1[Dim B], Table1[Ticket], Tickets[Ticket]; Table1[Dim A], 10 )
Using a measure:
Measure-When DimA-10 = IF ( HASONEVALUE ( Table1[Ticket] ), LOOKUPVALUE ( Table1[Dim B], Table1[Ticket], VALUES ( Tickets[Ticket] ), Table1[Dim A], 10 ) )
let me know if works.
Victor
Hello Victor!
Thanks for your support,
I don't quite got there yet, could you please check your formula spelling? Let's say that the left table is named TABLE A (wich is where I have the facts) and the right one is TABLE B (wich there's the dimension I want to bring to TABLE A)
@Tulio_DL,
Create the calculated column in TABLE A.
When DimA-10 = LOOKUPVALUE ( 'TABLE B'[DIM b], 'TABLE B'[Ticket], 'TABLE A'[Ticket], 'TABLE B'[DIM a], 10 )
Also you can change the second formula of Vvelarde to the following:
Measure-When DimA-10 = IF ( HASONEVALUE ( 'TABLE B'[Ticket] ), LOOKUPVALUE ( 'TABLE B'[DIM b], 'TABLE B'[Ticket], VALUES ( 'TABLE A'[Ticket] ), 'TABLE B'[DIM a], 10 ) )
Regards,
I can no wrap my head around the way you are using dimension and fact tables. I feel like you have them swapped.
Dimension tables should have unique id's...
Hello @Anonymous
It really looks like....... actually it is very confusing! BUT.... that's the only way a *** system gives me the information I need
='[
Well, given you DO have a table of unique values, I would go ahead and create the relationship between ticket id's. The 1 to many direction will be a bit opposite of what people think, but so be it.
Now, in your table of unique values, apparently you want a new calculated column... that looks up into the non-unique table, and does *what* exactly?
Yes @Anonymous! I have already established this relationship, and yes it's totally in the opposite way...
I would like to bring the information at 'DIMENSION table'[DIM b] into my 'FACT table'
considering that 'DIMENSION table'[DIM a] = 10 for example
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
93 | |
72 | |
58 |