topic Re: DAX formula in DAX Commands and Tips

DAX formula

jimpatel — Thu, 22 Sep 2022 15:10:10 GMT

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

Re: DAX formula

tamerj1 — Thu, 22 Sep 2022 16:21:54 GMT

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

Re: DAX formula

jimpatel — Fri, 23 Sep 2022 09:27:04 GMT

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

Thanks again

Re: DAX formula

jimpatel — Mon, 26 Sep 2022 16:04:20 GMT

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

Re: DAX formula

tamerj1 — Mon, 26 Sep 2022 15:57:19 GMT

jimpatel

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

Re: DAX formula

jimpatel — Mon, 26 Sep 2022 16:06:09 GMT

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

Re: DAX formula

tamerj1 — Mon, 26 Sep 2022 16:22:27 GMT

jimpatel

Please try

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

Re: DAX formula

jimpatel — Mon, 26 Sep 2022 16:40:59 GMT

Thanks for your reply,

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

Like this

Re: DAX formula

tamerj1 — Mon, 26 Sep 2022 16:48:05 GMT

jimpatel

Please try

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

Re: DAX formula

jimpatel — Tue, 27 Sep 2022 08:04:23 GMT

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

Re: DAX formula

tamerj1 — Tue, 27 Sep 2022 08:43:43 GMT

Hi jimpatel
Start from The Definitive Guide to DAX