Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI,
I need help on creating a DAX for filtering the following result in column E to H.
Basically, I need to create a 'Filter All' by Age and the year. And for the Age I would like it to ignore the calculation for the band Age_99 and be able to differentiate between two company type (A & B)
For Column F(Filter All), I believe the DAX language is something like this
Filter All= CALCULATE([Total Count],
Appreciate if someone can help on this.
Solved! Go to Solution.
Thanks @v-joesh-msft I think I have it figured out, that is to include the year parameter in the filter allexecept.
Total Age Count =
CALCULATE (
[Total Count],
FILTER ( ALLEXCEPT ( Table1, Table1[Year], Table1[CompanyType]),
Table1[AGE_ID] <> "AG_99" ))
I think something like this should work:
Filter 2017 =
IF( SELECTEDVALUE(Calendar[Year]) = 2017 ,
CALCULATE([Total Count],
FILTER(ALLEXCEPT(Table1,Table1[Company Type]),
Table1[AGE_ID] <> "AG_99"),
Calendar[Year] = 2017
),
BLANK()
)
Filter 2017/2018 =
IF( SELECTEDVALUE(Calendar[Year]) = 2017 || SELECTEDVALUE(Calendar[Year]) = 2018 ,
CALCULATE([Total Count],
FILTER(ALLEXCEPT(Table1,Table1[Company Type]),
Table1[AGE_ID] <> "AG_99"),
Calendar[Year] = 2017 || Calendar[Year] = 2018
),
BLANK()
)
Let's just say I do not want to explicitly call out the year, but it is a year filter that will aggregate these numbers accordingly.
Alright. In that case i will need to know how the selection logic is supposed to work. Could you give some examples of displayed columns with different selections on year?
Basically, rather than me specifying the year, I will allow the user to choose the year.
I wrote something based on what you have but I got the most outer left table output.
What I would want is the most outer right table output
Hi @echow ,
I think what you need is the following measure:
Total Age Count =
CALCULATE (
[Total Count],
FILTER ( ALLEXCEPT ( Table1, Table1[CompanyType] ), Table1[AGE_ID] <> "AG_99" )
)
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-joesh-msft I think I have it figured out, that is to include the year parameter in the filter allexecept.
Total Age Count =
CALCULATE (
[Total Count],
FILTER ( ALLEXCEPT ( Table1, Table1[Year], Table1[CompanyType]),
Table1[AGE_ID] <> "AG_99" ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |