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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emae613
Frequent Visitor

Dax to Calculate; Filter Multiple Columns with And

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! 

 

 

 

2 ACCEPTED SOLUTIONS

@emae613 
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.

View solution in original post

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

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

Hi @emae613 
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!

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. 

@emae613 
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.

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! 

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

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

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

Everything is working perfect! Thank you so much!

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?

@emae613 
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!

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

Anonymous
Not applicable

Hi @emae613 ,

 

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

 

YongChen_0-1646375981467.png

 

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

YongChen_1-1646376342892.png

 

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

YongChen_2-1646376500320.png

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

 

YongChen_3-1646376627945.png

 

 

Not sure if this is what you desired.

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors