Advanced multiple tables join's with conditions

[NMahi1703]

Hi Team, 
I have two tables as below, Need to filter the data based on few conditions as listed.
Table1:

ID	Account ID
1811	3630
3291	7625
5567	7625
5567	3631
3201	3631

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
7625	3291	P5	2	Warning
7625	3291	P6	9	Warning
8888	3291	P7	4	Critical
7625	4691	P8	2	Warning
7625		P9	6	Normal
7625		P10	7	Critical
7625		P11	8	Normal
3631	5567	P12	7	Normal
3631	5567	P13	10	Normal
3631		P14	5	Critical

Expected Output:

ID	Account ID	Name	Status	Acc Status
1811	3630	P1	Warning	Warning
1811	3630	P2	Warning	Normal
1811	3630	P3	Warning	Normal
1811	3630	P4	Warning	Normal
3291	7625	P5	Warning	Warning
3291	7625	P6	Warning	Warning
5567	7625	P5	Critical	Warning
5567	7625	P6	Critical	Warning
5567	7625	P8	Critical	Warning
5567	7625	P9	Critical	Normal
5567	7625	P10	Critical	Critical
5567	7625	P11	Critical	Normal
5567	3631	p12	Normal	Normal
5567	3631	p13	Normal	Normal
3201	3631	p12	Critical	Normal
3201	3631	p13	Critical	Normal
3201	3631	p14	Critical	Critical

Table1 Account ID is has many to many relationship with Account Number.

[v-yinliw-msft]

Hi @NMahi1703 ,

Could you please explain to me about the case1? What is the logic of the case1, i'm a little confused.

Best Regards,

Community Support Team _Yinliw

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

[NMahi1703]

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

[NMahi1703]

@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

[PaulDBrown]

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?

[NMahi1703]

Hi @PaulDBrown ,

Yes Status column is new column based on priorty of 1.Critical 2.Warning and 3. Normal

[PaulDBrown · Accepted solution]

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