DAX formula

[jimpatel]

Hi.

Thanks for looking at my post.

I am having 2 tables named as table 1 and table 2 but not related to each other.

What i am looking for DAX formula for "Table 1" column B as shown below, if any of the part number from column A in excel (which is table1  in power BI), match with column I in excel (which is table 2 in power bi) and if column J is True for any part number, then expected result should be true please. 

There are some instance where column J will have both true and false for same part numbers and in that case True will be over ridden please.

Any idea please

Thanks a lot and this will be massive step forward for me as i got stuck in this one for ages 

[tamerj1 · Accepted solution]

Hi @jimpatel 

is the true/false column from table 2 a text or boolean data type?

Trur/False =
IF (
    ISEMPTY (
        FILTER (
            Table2,
            Table2[Part Number] = Table1[Part Number]
                && Table2[True/False] = "TRUE"
        )
    ),
    "FALSE",
    "TRUE"
)

[tamerj1 · Accepted solution]

@jimpatel 

Please try

Status2 =
IF (
    ISEMPTY (
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Part Number] ) ),
            'Table'[Status] = "TRUE"
        )
    ),
    "FALSE",
    "TRUE"
)

[tamerj1 · Accepted solution]

Hi @jimpatel 

is the true/false column from table 2 a text or boolean data type?

Trur/False =
IF (
    ISEMPTY (
        FILTER (
            Table2,
            Table2[Part Number] = Table1[Part Number]
                && Table2[True/False] = "TRUE"
        )
    ),
    "FALSE",
    "TRUE"
)

[jimpatel]

Hi,

Sorry for reopening the post.

What do i need to change in the formula if i need to do the following condition please?

Note: All columns in the same table. The solution i am looking for is  "Status 2" column please

Issue is, this is from same table and all i need to compare is if "Part number" same , if yes look for "Status" and if any of the status is "True" for those part numbers then "Status 2" expected to be "true" if not "False"

Thanks a lot 

[tamerj1]

@jimpatel 

Sorry I did not understand. What do you mean by "if part number same"?

[jimpatel]

Thanks for your reply.

In the above example, 123 have one "True" in the "Status" and therefore "Status 2" is true for 123.
23 does not have any true at all, therefore status 2 will be false for that
43 have two true and status 2 will be true for that

Hope this helps

Note: Part numbers wont be in the ascending or descending orders and it will be clustered around the rows.

Thanks a lot

[tamerj1]

@jimpatel 

Please try

Trur/False =
IF (
    ISEMPTY (
        CALCULATETABLE (
            'Table',
            ALLEXCEPT ( 'Table', 'Table'[Part Number] ),
            Table2[True/False] = "TRUE"
        )
    ),
    "FALSE",
    "TRUE"
)

[jimpatel]

Thanks for your reply,

But i getting "Status 2" same as "Status" column for above formula

Like this

[tamerj1 · Accepted solution]

@jimpatel 

Please try

Status2 =
IF (
    ISEMPTY (
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Part Number] ) ),
            'Table'[Status] = "TRUE"
        )
    ),
    "FALSE",
    "TRUE"
)

[jimpatel]

This is really unbelievable work. Thanks a lot for your time. Can you please point me where i can learn these kind of formulas please?

Thanks a lot again

[tamerj1 · Accepted solution]

Hi @jimpatel 
Start from The Definitive Guide to DAX

[jimpatel]

Amazing work. Thanks a lot. This helping me to remove the barrier.

Thanks again