Forum Discussion
Advanced multiple tables join's with conditions
See if this works for you.
FIrst I created dimension tables using:
Then create the following measures:
Common Bill ID = VAR _Int = COUNTROWS ( INTERSECT ( VALUES ( 'Table 2'[BilliD] ), VALUES ( 'Table 1'[ID] ) ) ) RETURN IF ( _Int = 1, 1 )To use as a filter in the filter pane, use this measure and set the value to = 1:
Row Filter = VAR _AccIDs = COUNTROWS ( INTERSECT ( VALUES ( 'Account Table'[Account ID] ), CALCULATETABLE ( VALUES ( 'Table 2'[Account Number] ), FILTER ( ALLEXCEPT ( 'Table 2', 'Table 2'[Account Number] ), [Common Bill ID] = 1 ) ) ) ) RETURN SWITCH ( TRUE (), [Common Bill ID] = 1, 1, ISBLANK ( _AccIDs ), 1, 0 )To obtain the highest Status by account and filtered rows, use:
Status = VAR _ID = MAX ( 'Account Table'[Account ID] ) VAR _ImpValues = CALCULATETABLE ( VALUES ( 'Status Table'[Imp] ), FILTER ( ALLSELECTED ( 'Table 2' ), 'Table 2'[Account Number] = _ID ) ) VAR _MaxImp = MAXX ( _ImpValues, 'Status Table'[Imp] ) VAR _Status = LOOKUPVALUE ( 'Status Table'[Acc Status], 'Status Table'[Imp], _MaxImp ) RETURN IF ( ISBLANK ( MAX ( 'Table 2'[Version] ) ), BLANK (), _Status )and you will get:
Sample PBIX file attached
Hi v-yinliw-msft ,
Case 1 Explanation:
1. We have to create new column called "Status" in the output, which is based on "Acc Status" Column from Table2.
2. Tabe2 Acc Status Column may have values like Normal, Warning and Crtical. for Status column we have to consider the Priorty as 1. Critical , 2. Warning and 3.Normal. we need to display the status which has heigest priorty in Status Column.
3. In Case 1 Tabel1 Account Number is 3630 and ID is 3631. As mentioned Table1 Account ID is has relationship with Account Number.
4. Below are the column in table2
Account Number | BilliD | Product Name | Version | Acc Status |
3630 | NA | P1 | 1 | Warning |
3630 | NA | P2 | 2 | Normal |
3630 | NA | P3 | 2 | Normal |
3630 | NA | P4 | 10 | Normal |
Above ID value of table is 3631 which is not assosiated with billiD hence need to display all the rows , if ID is assoaited with billiD then we have to display that rows only.
hence outupt for case1 should be as below.
ID | Name | Status | Account ID | Acc Status |
1811 | P1 | Warning | 3630 | Warning |
1811 | P2 | Warning | 3630 | Normal |
1811 | P3 | Warning | 3630 | Normal |
1811 | P4 | Warning | 3630 | Normal |
v-yinliw-msft , Arul amitchandak FreemanZ Mikelytics MFelix PaulDBrown mangaus1111 Ashish_Mathur ryan_mayu PhilipTreacy
Dear Folks ,I stuck here can you guys provide any solutions.
Thanks
Sorry, I'm not too sure how you are calculating the "Status" field for each account. Is it just picking the highest from Critical > Warning > Normal for each account?
Hi PaulDBrown ,
Yes Status column is new column based on priorty of 1.Critical 2.Warning and 3. Normal
See if this works for you.
FIrst I created dimension tables using:
Then create the following measures:
Common Bill ID = VAR _Int = COUNTROWS ( INTERSECT ( VALUES ( 'Table 2'[BilliD] ), VALUES ( 'Table 1'[ID] ) ) ) RETURN IF ( _Int = 1, 1 )To use as a filter in the filter pane, use this measure and set the value to = 1:
Row Filter = VAR _AccIDs = COUNTROWS ( INTERSECT ( VALUES ( 'Account Table'[Account ID] ), CALCULATETABLE ( VALUES ( 'Table 2'[Account Number] ), FILTER ( ALLEXCEPT ( 'Table 2', 'Table 2'[Account Number] ), [Common Bill ID] = 1 ) ) ) ) RETURN SWITCH ( TRUE (), [Common Bill ID] = 1, 1, ISBLANK ( _AccIDs ), 1, 0 )To obtain the highest Status by account and filtered rows, use:
Status = VAR _ID = MAX ( 'Account Table'[Account ID] ) VAR _ImpValues = CALCULATETABLE ( VALUES ( 'Status Table'[Imp] ), FILTER ( ALLSELECTED ( 'Table 2' ), 'Table 2'[Account Number] = _ID ) ) VAR _MaxImp = MAXX ( _ImpValues, 'Status Table'[Imp] ) VAR _Status = LOOKUPVALUE ( 'Status Table'[Acc Status], 'Status Table'[Imp], _MaxImp ) RETURN IF ( ISBLANK ( MAX ( 'Table 2'[Version] ) ), BLANK (), _Status )and you will get:
Sample PBIX file attached