topic Re: Dax to Calculate; Filter Multiple Columns with And in DAX Commands and Tips

Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 04:49:35 GMT

Hello!

I have a table with the following columns: Permit Number, WOs, Net and I need to create a filter for my Card visual to get the correct results. I've tried to write numerous measures and can't get anything to work correctly. I am also not very good at this so I am sure I am doing something wrong.

I am trying to get the Total of the Net with the following filters applied: A filter to return the Total Net for Permit Number 5001 and only the total net for WO's 20, 21, and 22. And a filter to return the total net for Permit Numbers 2030, 2032, 3016, and 3017. Is this possible?

Thank you so much in advance!

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 06:50:51 GMT

Hi Anonymous ,

Maybe you can provide some sample data to make it easier for other people to solve. This is my sample data

If your Permit Number and WO are whole numbers, you can just use the slider in the slicer to choose the filters as below

If the Permit Number and WO are text, then you can hold CTRL when choosing the filter you desired as below

Owh ya, for the card visual I just drag the Sum of Net into the field. No measure needed.

Not sure if this is what you desired.

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 08:51:41 GMT

Hi Anonymous
Yes it is possible. If I fully understand your requirement then you can use the following codes (Make sure the columns Permit Number & WOs have integer data type not text)

Measure1 = VAR T1 = FILTER ( ALL ( 'Table' ), 'Table'[Permit Number] = 5001 ) VAR T2 = FILTER ( ALL ( 'Table' ), 'Table'[WOs] IN { 20, 21, 22 } ) VAR T3 = UNION ( T1, T2 ) VAR Result = SUMX ( T3, 'Table'[Net] ) RETURN Result

Measure2 = VAR T1 = FILTER ( ALL ( 'Table' ), 'Table'[Permit Number] IN { 2030, 2032, 3016, 3017 } ) VAR Result = SUMX ( T1, 'Table'[Net] ) RETURN Result

Please let me know if you face any trouble applying above or in case I misunderstood your requirement. Thanks and have a great day!

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 11:52:30 GMT

Thank you so much for your solution on this. Unfortunately, my WO column is a combination of Text and Whole Numbers so it won't let me convert it.

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 11:54:28 GMT

No problem. Just wrap the values inside the curly brackets with double quotes ""

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 12:42:41 GMT

Anonymous
As said. This is no problem. The code can be modified as follows:

Measure1 = VAR T1 = FILTER ( ALL ( 'Table' ), 'Table'[Permit Number] = 5001 ) VAR T2 = FILTER ( ALL ( 'Table' ), 'Table'[WOs] IN { "20", "21", "22" } ) VAR T3 = UNION ( T1, T2 ) VAR Result = SUMX ( T3, 'Table'[Net] ) RETURN Result

Measure2 = VAR T1 = FILTER ( ALL ( 'Table' ), 'Table'[Permit Number] IN { 2030, 2032, 3016, 3017 } ) VAR Result = SUMX ( T1, 'Table'[Net] ) RETURN Result

Please le me know if you need help.

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 13:12:03 GMT

Thank you so much! Measure 1 doesn't seem to be giving me the correct total for the data. Measure 2 worked and is giving me the correct total for the data. However, is there a way to combine the 2 measures? I need both of the measures on one card. Measure 2 is giving me the sum of all the net for the permits but it is missing the data associated with permit 5001 and the 3 associated work orders. Is there a way to fix measure 1 and apply both measures to the card?

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 13:21:25 GMT

Anonymous
The code for measure one actually sums up the Net values for all records that do not include permit No. 5001 and then computes (separately) the Net values for all records that have WO's code either 20, 21 or 22. Then it sums up both values together. If this is not what you need, please try to expain to me exactly waht your requirement is.
Thank you!

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 13:21:48 GMT

So this is giving me the correct result:

Measure 2 =

VAR T1 =

Filter (ALL('RF21 Master Dose File'), 'RF21 Master Dose File'[RWP_NUMBER] IN { 222032, 223016, 223017, 223032} )

VAR Result =

Sumx (T1, 'RF21 Master Dose File'[NET_DOSE_REM])

Return

Result

Then I have this measure that is giving me the correct result:

Calculate(

[NET_DOSE_REM],

KEEPFILTERS(

'RF21 Master Dose File'[RWP_NUMBER] = 225001 &&

'RF21 Master Dose File'[WORK_REQUEST_NUMBER] in {"64436091","64436532","55571027"})

)

If there is a way to combine these 2 measures it would be the perfect solution. Thanks!

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 13:24:29 GMT

What do you mean by "combine"? Add their values together?

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 13:26:59 GMT

Sorry about that. Yes, I need to add the values together so I can put the total into one card.

Re: Dax to Calculate; Filter Multiple Columns with And

tamerj1 — Fri, 04 Mar 2022 13:29:14 GMT

Yes you can create a new measure
Mearure3 = {Measur1] + [Measure2]
Then use it in your Card Visual

Re: Dax to Calculate; Filter Multiple Columns with And

Anonymous — Fri, 04 Mar 2022 13:39:34 GMT

Everything is working perfect! Thank you so much!