cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tulio_DL
Helper I
Helper I

Data modeling - Non unique values

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:

 Capturar1.JPG

 

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:

Capturar2.JPG

 

Do anyone knows how to do that in DAX? Tried the VLOOKUP formula but didn't succeeded.

 

Thanks in advance

 

 

 

 

2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@Tulio_DL

 

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




Lima - Peru

View solution in original post

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

1.PNG



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,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Vvelarde
Community Champion
Community Champion

@Tulio_DL

 

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




Lima - Peru

@Vvelarde

 

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
)

1.PNG



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,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Got it working.. Thank you all

 

@Vvelarde

@v-yuezhe-msft

Anonymous
Not applicable

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

 

='[

Anonymous
Not applicable

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.