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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jimpatel
Post Patron
Post Patron

DAX formula

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

 

jimpatel_0-1663859376663.png

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

 

3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

View solution in original post

@jimpatel 

Please try

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

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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

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"

jimpatel_0-1664208250991.png

 

Thanks a lot 

 

@jimpatel 

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

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

@jimpatel 

Please try

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

Thanks for your reply,

 

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

 

Like this

 

jimpatel_0-1664210450134.png

 

@jimpatel 

Please try

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

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

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

 

Thanks again

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.