topic Re: Compare values of 3 unrelated tables in DAX Commands and Tips

Compare values of 3 unrelated tables

Anonymous — Tue, 16 Jan 2024 09:26:30 GMT

Hi,

I have 3 tables :

Table 1: Host

Host	Model	Location
FCH243976J3	UCSX-X210-M7	US/01 NY 123
FCH26207HY7	UCSX-X210-M7	US/02 SAG
FCH26207J1Z	UCSC-C240-M5SX	US/03 RFTY 23
FCH26207HL9	UCSC-C240-M5SX	US/04 342
FCH26207HQC	UCSC-C220-M5SX	US/05/ 44F REY
FCH26207JC2	VxRail P570	US/06/4FS2C
FCH26207JAQ	VxRail P570	US/07/WD/34
FCH26207HQS	VxRail P570	US/08/SFO/34
FCH2611711A	VxRail P570	UK/01/BRK/34
FCH2611718F	VxRail P570	UK/02/LON/13
FCH26207J0B	VxRail P570	UK/03/YSH/09
FCH26207HQT	R760 vSAN Ready Node	UK/04/BIR/32
FCH26207J9D	R760 vSAN Ready Node	UK/05/BUK/12
FCH26207HQR	PowerEdge C6525	FR/39/GRO
FCH26207HL6	PowerEdge C6525	FR/40/BOD/33
FCH26207JK9	PowerEdge R6515	FR/41/EC3G
FLM24130BDG	PowerEdge R6515	FR/42/DCT
FCH243976DQ	PowerEdge R6515	FR/43/Q4F

Table 2 : SLA

Host	SLA
FCH243976J3	5/7
FCH26207HY7	7/7I
FCH26207J1Z	7/7R
FCH26207HL9	5/7
FCH26207HQC	5/7
FCH26207JC2	5/7
FCH26207JAQ	5/7
FCH26207HQS	5/7
FCH2611711A	7/7I
FCH2611718F	7/7I
FCH26207J0B	7/7I
FCH26207HQT	7/7I
FCH26207J9D	7/7R
FCH26207HQR	7/7R
FCH26207HL6	7/7R
FCH26207JK9	5/7
FLM24130BDG	5/7
FCH243976DQ	5/7

Table 3 : Prix

Model	Location	SLA	PRIX	Total
UCSX-X210-M7	US/01	5/7	1.65	225
UCSX-X210-M7	US/02	7/7I	1.65	225
UCSC-C240-M5SX	US/03	7/7R	2.25	225
UCSC-C240-M5SX	US/04	5/7	1.65	225
UCSC-C220-M5SX	US/05	5/7	1.65	225
VxRail P570	US/06	5/7	1.65	225
VxRail P570	US/07	5/7	1.65	225
VxRail P570	US/08	5/7	1.65	225
VxRail P570	UK/01	7/7I	2	431
VxRail P570	UK/02	7/7I	2	431
VxRail P570	UK/03	7/7I	2	431
R760 vSAN Ready Node	UK/04	7/7I	2	431
R760 vSAN Ready Node	UK/05	7/7R	2	431
PowerEdge C6525	FR/39	7/7R	2.25	521
PowerEdge C6525	FR/40	7/7R	2.25	521
PowerEdge R6515	FR/41	5/7	2.25	521
VxRail P570	FR/42	5/7	2.25	521
VxRail P570	FR/43	5/7	2.25	521

I would like to create a view from the table 1 "Host" as below :

Conditions of the same Host

Table 1[Model] = Table 3[Model]
Table 1[Location] contains Table 3[Location]
Table 2[SLA] = Table 3[SLA]

Host	Model	Location	SLA	PRIX	Total
FCH243976J3	UCSX-X210-M7	US/01 NY 123	5/7	1.65	225
FCH26207HY7	UCSX-X210-M7	US/02 SAG	7/7I	1.65	225
FCH26207J1Z	UCSC-C240-M5SX	US/03 RFTY 23	7/7R	2.25	225
FCH26207HL9	UCSC-C240-M5SX	US/04 342	5/7	1.65	225
FCH26207HQC	UCSC-C220-M5SX	US/05/ 44F REY	5/7	1.65	225
FCH26207JC2	VxRail P570	US/06/4FS2C	5/7	1.65	225
FCH26207JAQ	VxRail P570	US/07/WD/34	5/7	1.65	225
FCH26207HQS	VxRail P570	US/08/SFO/34	5/7	1.65	225
FCH2611711A	VxRail P570	UK/01/BRK/34	7/7I	2	431
FCH2611718F	VxRail P570	UK/02/LON/13	7/7I	2	431
FCH26207J0B	VxRail P570	UK/03/YSH/09	7/7I	2	431
FCH26207HQT	R760 vSAN Ready Node	UK/04/BIR/32	7/7I	2	431
FCH26207J9D	R760 vSAN Ready Node	UK/05/BUK/12	7/7R	2	431
FCH26207HQR	PowerEdge C6525	FR/39/GRO	7/7R	2.25	521
FCH26207HL6	PowerEdge C6525	FR/40/BOD/33	7/7R	2.25	521
FCH26207JK9	PowerEdge R6515	FR/41/EC3G	5/7	2.25	521
FLM24130BDG	PowerEdge R6515	FR/42/DCT
FCH243976DQ	PowerEdge R6515	FR/43/Q4F

Thank you in advance for your advice.

Re: Compare values of 3 unrelated tables

ValtteriN — Tue, 16 Jan 2024 11:08:33 GMT

Hi,

Here is one way to do this:

View =

var _vtable1 =

VAR _chost = MAX(Host[Host]) RETURN

ADDCOLUMNS(Host,"SLA Host",CALCULATE(MAX(SLA[SLA]),FILTER(SLA,Host[Host]=SLA[Host])))

RETURN

FILTER(CROSSJOIN(_vtable1,Prix),[SLA Host]=[SLA.1] && [Model]=[Model.1] && FIND([Location.1],[Location],,-1)>0)

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Compare values of 3 unrelated tables

Anonymous — Tue, 16 Jan 2024 12:59:15 GMT

Hi ValtteriN

Thank you for your quick reply.

Could you please sugges other solution if possible to avoid adding a table but only a Table view

Re: Compare values of 3 unrelated tables

ValtteriN — Tue, 16 Jan 2024 13:10:30 GMT

Hi Anonymous ,

In that case you can use MAXX with some modifications:

MAXX of view = var _vtable1 =

VAR _chost = MAX(Host[Host]) RETURN

ADDCOLUMNS(Host,"SLA Host",CALCULATE(MAX(SLA[SLA]),FILTER(SLA,Host[Host]=SLA[Host])))

var _vtable2=

FILTER(CROSSJOIN(_vtable1,Prix),[SLA Host]=[SLA.1] && [Model]=[Model.1] && FIND([Location.1],[Location],,-1)>0)

RETURN

MAXX(_vtable2,[PRIX])

This measure will return PRIX values based on the previous example.

using it in table view returns this:

Now you can do the same with total and add the columns you want.

Re: Compare values of 3 unrelated tables

Anonymous — Thu, 18 Jan 2024 08:55:38 GMT

Hi ValtteriN

The both solutions don't work in my real data.

Let me make it simple, i have 2 tables :

Table 1: Host

Host	Model	Location	SLA
FCH243976J3	UCSX-X210-M7	US/01 NY 123	5/7
FCH26207HY7	UCSX-X210-M7	US/02 SAG	7/7I
FCH26207J1Z	UCSC-C240-M5SX	US/03 RFTY 23	7/7R
FCH26207HL9	UCSC-C240-M5SX	US/04 342	5/7
FCH26207HQC	UCSC-C220-M5SX	US/05/ 44F REY	5/7
FCH26207JC2	VxRail P570	US/06/4FS2C	5/7
FCH26207JAQ	VxRail P570	US/07/WD/34	5/7
FCH26207HQS	VxRail P570	US/08/SFO/34	5/7
FCH2611711A	VxRail P570	UK/01/BRK/34	7/7I
FCH2611718F	VxRail P570	UK/02/LON/13	7/7I
FCH26207J0B	VxRail P570	UK/03/YSH/09	7/7I
FCH26207HQT	R760 vSAN Ready Node	UK/04/BIR/32	7/7I
FCH26207J9D	R760 vSAN Ready Node	UK/05/BUK/12	7/7R
FCH26207HQR	PowerEdge C6525	FR/39/GRO	7/7R
FCH26207HL6	PowerEdge C6525	FR/40/BOD/33	7/7R
FCH26207JK9	PowerEdge R6515	FR/41/EC3G	5/7
FLM24130BDG	PowerEdge R6515	FR/42/DCT	5/7
FCH243976DQ	PowerEdge R6515	FR/43/Q4F	5/7

Table 2 : Prix

Model	Location	SLA	PRIX	Total
UCSX-X210-M7	US/01	5/7	1.65	225
UCSX-X210-M7	US/02	7/7I	1.65	225
UCSC-C240-M5SX	US/03	7/7R	2.25	225
UCSC-C240-M5SX	US/04	5/7	1.65	225
UCSC-C220-M5SX	US/05	5/7	1.65	225
VxRail P570	US/06	5/7	1.65	225
VxRail P570	US/07	5/7	1.65	225
VxRail P570	US/08	5/7	1.65	225
VxRail P570	UK/01	7/7I	2	431
VxRail P570	UK/02	7/7I	2	431
VxRail P570	UK/03	7/7I	2	431
R760 vSAN Ready Node	UK/04	7/7I	2	431
R760 vSAN Ready Node	UK/05	7/7R	2	431
PowerEdge C6525	FR/39	7/7R	2.25	521
PowerEdge C6525	FR/40	7/7R	2.25	521
PowerEdge R6515	FR/41	5/7	2.25	521
VxRail P570	FR/42	5/7	2.25	521
VxRail P570	FR/43	5/7	2.25	521

I would like to add to table 1 the columns Prix and Total

Conditions of the same Host

Table 1[Model] = Table 2[Model]
Table 1[Location] contains Table 2[Location]
Table 1[SLA] = Table 2[SLA]

Host	Model	Location	SLA	PRIX	Total
FCH243976J3	UCSX-X210-M7	US/01 NY 123	5/7	1.65	225
FCH26207HY7	UCSX-X210-M7	US/02 SAG	7/7I	1.65	225
FCH26207J1Z	UCSC-C240-M5SX	US/03 RFTY 23	7/7R	2.25	225
FCH26207HL9	UCSC-C240-M5SX	US/04 342	5/7	1.65	225
FCH26207HQC	UCSC-C220-M5SX	US/05/ 44F REY	5/7	1.65	225
FCH26207JC2	VxRail P570	US/06/4FS2C	5/7	1.65	225
FCH26207JAQ	VxRail P570	US/07/WD/34	5/7	1.65	225
FCH26207HQS	VxRail P570	US/08/SFO/34	5/7	1.65	225
FCH2611711A	VxRail P570	UK/01/BRK/34	7/7I	2	431
FCH2611718F	VxRail P570	UK/02/LON/13	7/7I	2	431
FCH26207J0B	VxRail P570	UK/03/YSH/09	7/7I	2	431
FCH26207HQT	R760 vSAN Ready Node	UK/04/BIR/32	7/7I	2	431
FCH26207J9D	R760 vSAN Ready Node	UK/05/BUK/12	7/7R	2	431
FCH26207HQR	PowerEdge C6525	FR/39/GRO	7/7R	2.25	521
FCH26207HL6	PowerEdge C6525	FR/40/BOD/33	7/7R	2.25	521
FCH26207JK9	PowerEdge R6515	FR/41/EC3G	5/7	2.25	521
FLM24130BDG	PowerEdge R6515	FR/42/DCT
FCH243976DQ	PowerEdge R6515	FR/43/Q4F

Thank you

Re: Compare values of 3 unrelated tables

ValtteriN — Thu, 18 Jan 2024 09:43:30 GMT

Hi,

Here are measures for this:

Prix2 =

var _model = MAX(Host2[Model])

var _sla = MAX(Host2[SLA])

var _location = MAX(Host2[Location])

RETURN

CALCULATE(MAX(Prix2[PRIX]),FILTER(all(Prix2),Prix2[Model]=_model&&Prix2[SLA]=_sla && CONTAINSSTRING(_location,[Location])))

Total2 =

var _model = MAX(Host2[Model])

var _sla = MAX(Host2[SLA])

var _location = MAX(Host2[Location])

RETURN

CALCULATE(MAX(Prix2[Total]),FILTER(all(Prix2),Prix2[Model]=_model&&Prix2[SLA]=_sla && CONTAINSSTRING(_location,[Location])))

End result:

You can also consider using functions like LEFT if the longer location always starts with the shorter location text.

Re: Compare values of 3 unrelated tables

Anonymous — Thu, 18 Jan 2024 12:05:47 GMT

Thank you ValtteriN

It works when I remove MAX

Prix2 =

var _model =Host2[Model]

var _sla =Host2[SLA]

var _location = Host2[Location]

RETURN

CALCULATE(MAX(Prix2[PRIX]),FILTER(all(Prix2),Prix2[Model]=_model&&Prix2[SLA]=_sla && CONTAINSSTRING(_location,[Location])))