The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hey guys,
I have a table with the results of certain inspections of different parts.
If the inspection was NG ::> status = X
If the inspection was Ok or concession, status = O or C
I would like to have a measure that calculates how many parts are still open (so X was given, but not yet O)
Part 1 X 01/01/2020
Part 2 X 02/01/2020
Part 1 X 03/ 01/2020
Part 3 X 03/01/2020
Part 2 O 05/01/2020
Part 4 O 06/01/2020
So for the example above: the measure should show 2 parts (as part 1 & part 3 have not received O yet), and part 2 has received O so should be excluded from the data.
Solved! Go to Solution.
Hi @jasperdavid ,
You need a measure as below:
Measure =
VAR a =
CALCULATE (
COUNT ( 'Table'[status] ),
ALLEXCEPT ( 'Table', 'Table'[Part] ),
'Table'[status] <> "O"
)
VAR b =
CALCULATE ( COUNT ( 'Table'[status] ), ALLEXCEPT ( 'Table', 'Table'[Part] ) )
RETURN
IF ( a = b, a, BLANK () )
Finally ,you will see:
For the related .pbix file,pls click here.
Hi @jasperdavid ,
You need a measure as below:
Measure =
VAR a =
CALCULATE (
COUNT ( 'Table'[status] ),
ALLEXCEPT ( 'Table', 'Table'[Part] ),
'Table'[status] <> "O"
)
VAR b =
CALCULATE ( COUNT ( 'Table'[status] ), ALLEXCEPT ( 'Table', 'Table'[Part] ) )
RETURN
IF ( a = b, a, BLANK () )
Finally ,you will see:
For the related .pbix file,pls click here.
try to have a measure like this
calculate(countrows(table),allexcept(table,table[part]),table[status]="O"), and use this to filter
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |