March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi, I'm using the below dax to exclude the rows that contain certain text so that i could get the required value. Here's the dax: Column = IF( Sheet1[OD group] = "0-30", IF( Sheet1[Outcome] = "",
IF( not CONTAINS(Sheet1,Sheet1[UltimateParentDUNSNumber], "NSA"), Sheet1[Total Value] ) ))
But, I'm not getting the required outcome through dax. I still have to use the manual filters to get the value. Any help on getting the result through dax would be appreciated.
Thanks in advance.
Solved! Go to Solution.
Hi @Anand24 @harshnathani ,
Thank you for your help with my query.
I've made minor changes to the DAX formula and now I can see the expected outcome. Here's what I used:
Column = IF(
AND(Sheet1[Days to Decision] = "0-30", NOT(CONTAINSSTRING(Sheet1[UltimateParentDUNSNumber],"NSA"))),
IF(Sheet1[Outcome] = "",
Sheet1[Total Value]))
Thanks again for your efforts!!!
Regards,
Mahesh
Hi @Anonymous ,
Can you share sample data and the expected output?
Regards,
HN
@harshnathani Hi, this is a confidential data and i do not have any sample data as such. However, I can give you a brief about it.
IF( Sheet1[OD group] = "0-30", IF( Sheet1[Outcome] = "",
IF( not CONTAINS(Sheet1,Sheet1[UltimateParentDUNSNumber], "NSA"), Sheet1[Total Value] ) ))
the outcome I'm anticipating is that I should exclude all the "NSA" listed data (regardless of the placing of the alphabets onin a cell) and only calculate the rest of the data based on "OD Group"
Hi @Anonymous ,
Let me know if this works.
Column =
SWITCH(
TRUE(),
'Table'[OD group] = "0-30" && CONTAINSSTRING('Table'[UltimateParentDUNSNumber],"NSA"), 'Table'[Total Value],
BLANK()
)
If not please create some sample data and share the output needed.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@harshnathani Thank you for sharing the DAX for the query. However, I'd the formula for not calculating the "NSA".
"UltimateParentDUNSNumber" contains Numeric and AlphaNumeric data. In AlphaNumberic, I should "Exclude" all the data which contains "NSA" in them.
The formula you've provided is calculating all the rows which contains "NSA" in them. This should be the otherway around. Below is the sample data on how it looks and the outcome I'm expecting.
Regards,
Mahesh
UltimateParentDUNSNumber | Total Value | OD Group | outcome |
NSA45476749 | 100 | 0-30 | |
Elim-NSA67454 | 745 | 0-30 | |
4569485 | 56873 | 0-30 | 56873 |
5738456 | 475834 | 0-30 | 475834 |
4735436 | 4564 | 0-30 | 4564 |
Hi @Anonymous ,
You can use the below DAX in a measure:
Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
@Anand24 I've tried both the formulas provided and I'm seeing only one output with both the formulas. below is the screenshot of the output
Hi @Anand24 @harshnathani ,
Thank you for your help with my query.
I've made minor changes to the DAX formula and now I can see the expected outcome. Here's what I used:
Column = IF(
AND(Sheet1[Days to Decision] = "0-30", NOT(CONTAINSSTRING(Sheet1[UltimateParentDUNSNumber],"NSA"))),
IF(Sheet1[Outcome] = "",
Sheet1[Total Value]))
Thanks again for your efforts!!!
Regards,
Mahesh
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
90 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |