Data Modeling Issue

[Asghararslan]

Good Evening All,

 I’m facing an issue with tables modeling between lookup & data table.

Below is on the of shortest example of my report.

Data Table

Aircraft	Staff	Flts	Flts	Flts	Flts	Flts	Flts
A300	4	1x	2x	3x	4x	5x	6x
A310	6	1x	2x	3x	4x	5x	6x
A380	9	1x	2x	3x	4x	5x	6x
A320	5	1x	2x	3x	4x	5x	6x
B727	4	1x	2x	3x	4x	5x	6x
B738	6	1x	2x	3x	4x	5x	6x

Lookup Table                                                                                                                                                                    

Flts	Number
1x	1
2x	2
3x	3
4x	4
5x	5
6x	6

I’m trying to create a connection between lookup table Flts with Data Table Flts (All Flts column not only one (data table) ). Is there any chance to create this connection in modelling between one column – Many column. I have tried in modelling one to many but no luck.

Your valuable assistant will be highly appreciated in this.

[v-henryk-mstf]

Hi @Asghararslan ,

Whether you can provide the right solution to help the other members find it more quickly. For new problems you encounter, you can post in the corresponding forum, our engineers will promptly solve for you.

Looking forward to your feedback.😊

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-henryk-mstf]

Hi @Asghararslan ,

According to your description, try the following formula:

1 =
VAR sel =
    CALCULATE (
        MAX ( 'Lookup Table'[Number] ),
        FILTER (
            ALL ( 'Lookup Table' ),
            SELECTEDVALUE ( 'Lookup Table'[Fits] ) = 'Lookup Table'[Fits]
        )
    )
RETURN
    sel * MAX ( 'Data Table'[Staff] )

If the problem persists, can you provide a screenshot of the expected results so that I can do further testing. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Asghararslan]

HI Henry,

i have fixed the problem, right now im facing another issue.

[Asghararslan]

i would like to add this here that,

im using lookup table Flts column as a filter also, so in filteration if i click on 2x, the result should be the multiple of (number* staff), same should work for 1x,3x etc. i hope you get my query

[amitchandak]

@Asghararslan , I think so. Check the attached file after signature. I renamed a few columns and unpivoted those

[Asghararslan]

Oh no, Actually in my actual file i have around 425Rows, if i go for unpivoting all of these then i have to perform a lot of work in my key measures.

how can i manage this in userrelationship?

[Asghararslan]

Hi, unpivoting only to FLTS Column will work? 

[amitchandak]

@Asghararslan , You should unpivot the all Flts column in table 1

Another way is inactive connection and userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...