Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello I have the following Dax which aims at Summarizing all aircraft who that have a value of $100,000 or more per Class which also have a Correct Company Name, but my output is also including $110,000 which is being calculated (Bellanca $20,000 plus Lancair $90,000). Although it is true that they are part of the Single Piston Class and are over $100,000, they are not in my Correct Company Name list and therfore I do not want them to be calculated. What can I add to this Dax so it avoids calculating a blank value in Correct Company Name?
100k+ (SUMMARIZE) =
SUMX(
FILTER(
SUMMARIZE(
Database,
'Class Lookup'[Class],
CompanyNameLookup[Correct Company Name],
"_1",
SUM(Database[Amount])
),
[_1]>=100000
)
,[_1]
)
THe correct answer I am looking for should not include the calculation of the SIngle Piston Column which has a blank value under Correct Company name column.
My database and Lookup tables look like this.
Thank you!
Solved! Go to Solution.
Please try this expression instead. You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.
100k+ (SUMMARIZE) =
SUMX (
FILTER (
FILTER (
SUMMARIZE (
Database,
'Class Lookup'[Class],
CompanyNameLookup[Correct Company Name],
"_1", SUM ( Database[Amount] )
),
NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
),
[_1] >= 100000
),
[_1]
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression instead. You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.
100k+ (SUMMARIZE) =
SUMX (
FILTER (
FILTER (
SUMMARIZE (
Database,
'Class Lookup'[Class],
CompanyNameLookup[Correct Company Name],
"_1", SUM ( Database[Amount] )
),
NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
),
[_1] >= 100000
),
[_1]
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous -
Please try this
100k+ (SUMMARIZE) =
SUMX (
FILTER (
SUMMARIZE (
Database,
'Class Lookup'[Class],
CompanyNameLookup[Correct Company Name],
"_1", SUM ( Database[Amount] )
),
[_1] >= 100000 && NOT( ISBLANK( [Correct Company Name] ) )
),
[_1]
)
If that doesn't work, please provide you data in a format that can be copy/pasted into Excel or Power BI, rather than a screen shot.
Hope this helps
David
Thanks to both of you. I have taken an advanced Dax Course, but the difficulty for me is how to combine multiple Dax Expressions into the correct order to make them work for me. For instance I know I can get my answer by deselecting blank on my filter so I was trying to incorporate it in the Dax, but not sure where it needed to go in my formula. Thanks again!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |