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.
Hello,
I am currently facing a problem with eliminating only one specific filter from a measure and keep all other possible filters.
Below an example:
ID | Production_Line | Defect | Produced quantity (meter) | DateProd |
511162122 | EXT08 | OK | 8,494.00 | 18/04/2022 |
511162993 | EXT01 | Spark | 8,463.00 | 18/04/2022 |
511162992 | EXT01 | Node | 8,463.00 | 18/04/2022 |
511162128 | EXT05 | Spark | 8,494.00 | 18/04/2022 |
511162121 | EXT06 | Spark | 4,344.00 | 18/04/2022 |
511162126 | EXT01 | OK | 8,494.00 | 18/04/2022 |
511162124 | EXT02 | OK | 4,149.00 | 17/04/2022 |
511162127 | EXT01 | OK | 8,494.00 | 17/04/2023 |
511162033 | EXT01 | Diameter | 112.00 | 17/04/2024 |
511162034 | EXT01 | Sparl | 93.00 | 17/04/2025 |
511162021 | EXT07 | OK | 4,990.00 | 18/04/2022 |
511162023 | EXT07 | OK | 4,989.00 | 16/04/2022 |
511162024 | EXT07 | OK | 4,628.00 | 16/04/2023 |
511162020 | EXT07 | OK | 4,989.00 | 16/04/2024 |
511162025 | EXT08 | Node | 165.00 | 16/04/2025 |
511162035 | EXT08 | OK INC | 1,122.00 | 16/04/2026 |
511162028 | EXT02 | OK INC | 1,200.00 | 16/04/2027 |
511162022 | EXT02 | OK | 4,990.00 | 18/04/2022 |
511162036 | EXT01 | Node | 725.00 | 15/04/2022 |
511162032 | EXT05 | Node | 3,045.00 | 15/04/2023 |
511162037 | EXT01 | Node | 70.00 | 15/04/2024 |
511162031 | EXT01 | OK | 3,507.00 | 15/04/2025 |
511162038 | EXT01 | OK | 4,995.00 | 15/04/2026 |
511162039 | EXT01 | OK | 4,989.00 | 18/04/2022 |
511162029 | EXT01 | Spark | 168.00 | 18/04/2022 |
So, I want to apply this equation: sum(Production quantity for specefic selected defect) / sum(production quantity, taking into account all types of defects), also i want the equation to be succeptible to change each time i choose a specific defect on slicer.
However i want to keep all other possible filters applicable (for example: dateprod, and line).
Thank you in Advance
Solved! Go to Solution.
Hi @Mouadh_M ,
If you want to get toe top defect by measure, you can try to create a rank measure and then get defect whose rank =1.
Rank = RANKX(CALCULATETABLE( VALUES('Table'[Defect]),ALL('Table')),[Measure],,DESC,Dense)
Top Defect =
CALCULATE(MAX('Table'[Defect]),FILTER(VALUES('Table'[Defect]),[Rank] = 1))
Result is as below.
By Default:
Select Production line:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Mouadh_M ,
Were you thinking of something like this? 🙂
Here the DAX:
Measure = DIVIDE ( CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ), CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table) ) )
Note, the measure is pretty dynamic and works for other dimensions, too. Like this:
This will lead to the following behaviour when dragging in another attribute:
If you would like to have it more fixed like this (see measure2)...
... I'd recommend the following DAX code instead:
Measure2 = VAR _a =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALLEXCEPT( Table, Table[Defect] ) ) VAR _b =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALL ( Table) ) RETURN DIVIDE ( _a, _b )
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hello tomfox,
I really appreciate your help, your suggestion was helpful, but i found that if i use the defect filter it gave me a better result:
Measure = DIVIDE ( CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ), CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table[Defect]) )
)
can you help me figure how to plot automatically the top defect by measure?
thank you in advance
Hi @Mouadh_M ,
If you want to get toe top defect by measure, you can try to create a rank measure and then get defect whose rank =1.
Rank = RANKX(CALCULATETABLE( VALUES('Table'[Defect]),ALL('Table')),[Measure],,DESC,Dense)
Top Defect =
CALCULATE(MAX('Table'[Defect]),FILTER(VALUES('Table'[Defect]),[Rank] = 1))
Result is as below.
By Default:
Select Production line:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your kind contributions.
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |