Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vnqt
Helper V
Helper V

Compare values of 3 unrelated tables

Hi,

 

I have 3 tables :

Table 1: Host

HostModelLocation
FCH243976J3UCSX-X210-M7US/01 NY 123
FCH26207HY7UCSX-X210-M7US/02 SAG
FCH26207J1ZUCSC-C240-M5SXUS/03 RFTY 23 
FCH26207HL9UCSC-C240-M5SXUS/04 342
FCH26207HQCUCSC-C220-M5SXUS/05/ 44F REY
FCH26207JC2VxRail P570US/06/4FS2C
FCH26207JAQVxRail P570US/07/WD/34
FCH26207HQSVxRail P570US/08/SFO/34
FCH2611711AVxRail P570UK/01/BRK/34
FCH2611718FVxRail P570UK/02/LON/13
FCH26207J0BVxRail P570UK/03/YSH/09
FCH26207HQTR760 vSAN Ready NodeUK/04/BIR/32
FCH26207J9DR760 vSAN Ready NodeUK/05/BUK/12
FCH26207HQRPowerEdge C6525FR/39/GRO
FCH26207HL6PowerEdge C6525FR/40/BOD/33
FCH26207JK9PowerEdge R6515FR/41/EC3G
FLM24130BDGPowerEdge R6515FR/42/DCT
FCH243976DQPowerEdge R6515FR/43/Q4F

 

 

Table 2 : SLA

 

HostSLA
FCH243976J35/7
FCH26207HY77/7I
FCH26207J1Z7/7R
FCH26207HL95/7
FCH26207HQC5/7
FCH26207JC25/7
FCH26207JAQ5/7
FCH26207HQS5/7
FCH2611711A7/7I
FCH2611718F7/7I
FCH26207J0B7/7I
FCH26207HQT7/7I
FCH26207J9D7/7R
FCH26207HQR7/7R
FCH26207HL67/7R
FCH26207JK95/7
FLM24130BDG5/7
FCH243976DQ5/7

 

Table 3 : Prix

ModelLocationSLAPRIXTotal
UCSX-X210-M7US/015/71.65225
UCSX-X210-M7US/027/7I1.65225
UCSC-C240-M5SXUS/037/7R2.25225
UCSC-C240-M5SXUS/045/71.65225
UCSC-C220-M5SXUS/055/71.65225
VxRail P570US/065/71.65225
VxRail P570US/075/71.65225
VxRail P570US/085/71.65225
VxRail P570UK/017/7I2431
VxRail P570UK/027/7I2431
VxRail P570UK/037/7I2431
R760 vSAN Ready NodeUK/047/7I2431
R760 vSAN Ready NodeUK/057/7R2431
PowerEdge C6525FR/397/7R2.25521
PowerEdge C6525FR/407/7R2.25521
PowerEdge R6515FR/415/72.25521
VxRail P570FR/425/72.25521
VxRail P570FR/435/72.25521

 

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]

 

HostModelLocationSLAPRIXTotal
FCH243976J3UCSX-X210-M7US/01 NY 1235/71.65225
FCH26207HY7UCSX-X210-M7US/02 SAG7/7I1.65225
FCH26207J1ZUCSC-C240-M5SXUS/03 RFTY 23 7/7R2.25225
FCH26207HL9UCSC-C240-M5SXUS/04 3425/71.65225
FCH26207HQCUCSC-C220-M5SXUS/05/ 44F REY5/71.65225
FCH26207JC2VxRail P570US/06/4FS2C5/71.65225
FCH26207JAQVxRail P570US/07/WD/345/71.65225
FCH26207HQSVxRail P570US/08/SFO/345/71.65225
FCH2611711AVxRail P570UK/01/BRK/347/7I2431
FCH2611718FVxRail P570UK/02/LON/137/7I2431
FCH26207J0BVxRail P570UK/03/YSH/097/7I2431
FCH26207HQTR760 vSAN Ready NodeUK/04/BIR/327/7I2431
FCH26207J9DR760 vSAN Ready NodeUK/05/BUK/127/7R2431
FCH26207HQRPowerEdge C6525FR/39/GRO7/7R2.25521
FCH26207HL6PowerEdge C6525FR/40/BOD/337/7R2.25521
FCH26207JK9PowerEdge R6515FR/41/EC3G5/72.25521
FLM24130BDGPowerEdge R6515FR/42/DCT   
FCH243976DQPowerEdge R6515FR/43/Q4F   

 

Thank you in advance for your advice.

Tg

1 ACCEPTED SOLUTION

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:
ValtteriN_0-1705570960553.png

 


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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ValtteriN
Super User
Super User

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:


ValtteriN_0-1705403310146.png

 

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/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 

vnqt_0-1705409930900.png

 

Tg 

 

 

Hi @vnqt ,

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:

ValtteriN_0-1705410586319.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ValtteriN 

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

Let me make it simple, i have 2 tables : 

 

Table 1: Host

 

HostModelLocationSLA
FCH243976J3UCSX-X210-M7US/01 NY 1235/7
FCH26207HY7UCSX-X210-M7US/02 SAG7/7I
FCH26207J1ZUCSC-C240-M5SXUS/03 RFTY 23 7/7R
FCH26207HL9UCSC-C240-M5SXUS/04 3425/7
FCH26207HQCUCSC-C220-M5SXUS/05/ 44F REY5/7
FCH26207JC2VxRail P570US/06/4FS2C5/7
FCH26207JAQVxRail P570US/07/WD/345/7
FCH26207HQSVxRail P570US/08/SFO/345/7
FCH2611711AVxRail P570UK/01/BRK/347/7I
FCH2611718FVxRail P570UK/02/LON/137/7I
FCH26207J0BVxRail P570UK/03/YSH/097/7I
FCH26207HQTR760 vSAN Ready NodeUK/04/BIR/327/7I
FCH26207J9DR760 vSAN Ready NodeUK/05/BUK/127/7R
FCH26207HQRPowerEdge C6525FR/39/GRO7/7R
FCH26207HL6PowerEdge C6525FR/40/BOD/337/7R
FCH26207JK9PowerEdge R6515FR/41/EC3G5/7
FLM24130BDGPowerEdge R6515FR/42/DCT5/7
FCH243976DQPowerEdge R6515FR/43/Q4F5/7

 

Table 2 : Prix

ModelLocationSLAPRIXTotal
UCSX-X210-M7US/015/71.65225
UCSX-X210-M7US/027/7I1.65225
UCSC-C240-M5SXUS/037/7R2.25225
UCSC-C240-M5SXUS/045/71.65225
UCSC-C220-M5SXUS/055/71.65225
VxRail P570US/065/71.65225
VxRail P570US/075/71.65225
VxRail P570US/085/71.65225
VxRail P570UK/017/7I2431
VxRail P570UK/027/7I2431
VxRail P570UK/037/7I2431
R760 vSAN Ready NodeUK/047/7I2431
R760 vSAN Ready NodeUK/057/7R2431
PowerEdge C6525FR/397/7R2.25521
PowerEdge C6525FR/407/7R2.25521
PowerEdge R6515FR/415/72.25521
VxRail P570FR/425/72.25521
VxRail P570FR/435/72.25521

 

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]

 

HostModelLocationSLAPRIXTotal
FCH243976J3UCSX-X210-M7US/01 NY 1235/71.65225
FCH26207HY7UCSX-X210-M7US/02 SAG7/7I1.65225
FCH26207J1ZUCSC-C240-M5SXUS/03 RFTY 23 7/7R2.25225
FCH26207HL9UCSC-C240-M5SXUS/04 3425/71.65225
FCH26207HQCUCSC-C220-M5SXUS/05/ 44F REY5/71.65225
FCH26207JC2VxRail P570US/06/4FS2C5/71.65225
FCH26207JAQVxRail P570US/07/WD/345/71.65225
FCH26207HQSVxRail P570US/08/SFO/345/71.65225
FCH2611711AVxRail P570UK/01/BRK/347/7I2431
FCH2611718FVxRail P570UK/02/LON/137/7I2431
FCH26207J0BVxRail P570UK/03/YSH/097/7I2431
FCH26207HQTR760 vSAN Ready NodeUK/04/BIR/327/7I2431
FCH26207J9DR760 vSAN Ready NodeUK/05/BUK/127/7R2431
FCH26207HQRPowerEdge C6525FR/39/GRO7/7R2.25521
FCH26207HL6PowerEdge C6525FR/40/BOD/337/7R2.25521
FCH26207JK9PowerEdge R6515FR/41/EC3G5/72.25521
FLM24130BDGPowerEdge R6515FR/42/DCT   
FCH243976DQPowerEdge R6515FR/43/Q4F   

 

Thank you 

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:
ValtteriN_0-1705570960553.png

 


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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors