DAX-LOOKUP with two different conditions in Power BI

[Saxon10]

Hi,

I have a two tables are Data and Report. 

Data:

In Data tables does contain duplicate line item

Team Code	Place Code	District Code1	District Code2	District Code3	District Code Text
1238	SRH	120	210	450	TRP
1238	CHR	34	45	86	DDD
1245	SRH	890	340	560
1245	CHR	780	580	120	TRP
1252	CHR	344	657	680	DDD
1269	CHR	0	0	0
890-RTU	SRH	458	543	346	DDD
670	SRH	10	10	10	DDD

Report;

Report table does not contain duplicate line item. 

Team Code

1238

1245

1252

1269

890-RTU

671

Result:

Here I want DAX and M language solution (two different method) to achieve my result (not visualisation measure). I would like to added 5 additional columns in my result table by using DAX and also M language (two different method). I don’t want to use the merge query options because it will create lot of duplication column.

Team Code	Place Code	District Code1	District Code2	District Code3	District Code Text
1238	SRH	120	210	450	TRP
1245	SRH	890	340	560
1252	CHR	344	657	680	DDD
1269	CHR	0	0	0
890-RTU	SRH	458	543	346	DDD
671	SRH	NA	NA	NA	NA

Criteria;

I would like to bring the Plant code, District code1, District code2 and District code3 and district code text from data table to report file with following conditions.

In order to pull the result (Plant code, District code1, District code2 and District code3 and district code text) from data table to report table, My first priority is “SRH” and second priority is “CHR”, if can't found the plant code “SRH” and “CHR” in data table then return is "NA".

Example1; Item 1238 does contain with two different plant code in Data table which is "SRH" and "CHR". In this case I want to pull the data against the "SRH" (Plant code, District code1, District code2 and District code3 and district code text) and ignore the "CHR".

Example2; Item 1252 cannot found the plant code "SRH" in data table so I want to pull the data against the “CHR” (Plant code, District code1, District code2 and District code3 and district code text)

Example3; Item 671 cannot found the plant code "SRH" or "CHR" in data table therefore return the result is "NA".

[Pragati11 · Accepted solution]

Hi @Saxon10 ,

Consider the input tables as follows:

I have created a relationship between these 2 tables on the common "Team Code" column.

Now create following calculated columns in your REPORT TABLE:

containsSRH =

IF(CONTAINS(testLookup, testLookup[Place Code], "SRH") = TRUE(), "SRH Exists", "No SRH")

containsCHR =

IF(CONTAINS(testLookup, testLookup[Place Code], "CHR") = TRUE(), "CHR Exists", "No CHR")

Now create a 3rd calculated column in the REPORT TABLE:

calcColumn =
IF (
    testLookupTable2[containsSRH] = "SRH Exists",
    IF (
        RELATED ( testLookup[Place Code] ) = "NA",
        "SRH",
        IF (
            testLookupTable2[containsCHR] = "CHR Exists",
            IF (
                RELATED ( testLookup[Place Code] ) = "NA",
                "CHR",
                IF (
                    testLookupTable2[containsSRH] = "No SRH"
                        && testLookupTable2[containsCHR] = "No CHR",
                    "NA",
                    RELATED ( testLookup[Place Code] )
                )
            )
        )
    )
)

Now create a table visual and move the "Team Code" and "calcColumn" from REPORT TABLE and rest of the other columns from the DATA TABLE. You will get the desired output:

I have attached the sample file for this scenario I created:

https://we.tl/t-2MuMW8laFT

Thanks,

Pragati

[Saxon10]

Hi,

Any advise please.

In Excel I am applying the following formula but I don't know how to build the same thing Power BI DAX.

Place Code B2=IF(COUNTIFS(Data!$A$2:$A$9,$A2,Data!$B$2:$B$9,"SRH"),"SRH",IF(COUNTIFS(Data!$A$2:$A$9,$A2,Data!$B$2:$B$9,"CHR"),"CHR","NA"))

District Code1

C2=IF($B2="NA","NA",IF(LEN(LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!C$2:C$9)),LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!C$2:C$9),""))

District Code2

D2=IF($B2="NA","NA",IF(LEN(LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!D$2:D$9)),LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!D$2:D$9),""))

District Code3

E2=IF($B2="NA","NA",IF(LEN(LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!E$2:E$9)),LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!E$2:E$9),""))

District Code Text

F2=IF($B2="NA","NA",IF(LEN(LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!F$2:F$9)),LOOKUP(2,1/((Data!$A$2:$A$9=$A2)*(Data!$B$2:$B$9=$B2)),Data!F$2:F$9),""))

file attached here https://www.dropbox.com/s/brwzoqd4qeyi2jz/INDEX%20MATCH-ORDER%20WISE-27.10.2020%20%28NFS%29.xlsx?dl=...

[Saxon10]

Hi,

i found similer query but not exactly one I am looking for but somthing like this https://community.powerbi.com/t5/Desktop/Lookup-another-table-with-Multiple-Values-if-one-value-is/m...

[Pragati11]

Hi @Saxon10 ,

Is there a relationship between these 2 tables?

[Saxon10]

Hi.

Thanks for your reply.

there is relationship that's called "Item" in between two tables.

[Pragati11 · Accepted solution]

Hi @Saxon10 ,

Consider the input tables as follows:

I have created a relationship between these 2 tables on the common "Team Code" column.

Now create following calculated columns in your REPORT TABLE:

containsSRH =

IF(CONTAINS(testLookup, testLookup[Place Code], "SRH") = TRUE(), "SRH Exists", "No SRH")

containsCHR =

IF(CONTAINS(testLookup, testLookup[Place Code], "CHR") = TRUE(), "CHR Exists", "No CHR")

Now create a 3rd calculated column in the REPORT TABLE:

calcColumn =
IF (
    testLookupTable2[containsSRH] = "SRH Exists",
    IF (
        RELATED ( testLookup[Place Code] ) = "NA",
        "SRH",
        IF (
            testLookupTable2[containsCHR] = "CHR Exists",
            IF (
                RELATED ( testLookup[Place Code] ) = "NA",
                "CHR",
                IF (
                    testLookupTable2[containsSRH] = "No SRH"
                        && testLookupTable2[containsCHR] = "No CHR",
                    "NA",
                    RELATED ( testLookup[Place Code] )
                )
            )
        )
    )
)

Now create a table visual and move the "Team Code" and "calcColumn" from REPORT TABLE and rest of the other columns from the DATA TABLE. You will get the desired output:

I have attached the sample file for this scenario I created:

https://we.tl/t-2MuMW8laFT

Thanks,

Pragati

[Saxon10]

Hi,

Thanks for your reply and help. It's I like way of your apporach. 

Is that any chance could you please help DAX function solution. The reason I am requesting different solution because I don't want to change the report table structure because it's already refered some other reports so it will affect my original report. 

I want only DAX expression (I don't want visualizations output).

I would like to create 5 new column by using DAX function (5 different DAX expression formula according to the header Place code, District code 1 to 3 and district code text) therefore I can refer this file when I needed. 

[Pragati11 · Accepted solution]

Hi @Saxon10 ,

The solution what I have given above is in DAX code.

Also, as you have mentioned, you need 5 new columns; 5 new columns will modify the schema of your table as using DAX you will be adding new columns to it.

So, achieving your desired result without adding new columns - not sure if that is possible.

Another thing, DAX adds column on your table which is not shown in Query Editor. The reason is query editor shows your actual data structure. So the underlying actual data structure of your table will never change.

DAX is more around adding new calculations to your tables. Also adding new columns to a table won't affect the other existing reports until they are used those existing reports.

Hope that makes sense.

Thanks,

Pragati