Forum Discussion
Min value count data in DAX
Hello,
I want dax calculation for min status value from specific Item Segment
below is my sample data.
| Id | Item | Sub Item | Item Status | Description for Count the Value | Expected Result/OP In DAX As below |
| 1 | Item1 | 110 | 0 | For Item 1 Min Status is 0 | Total Count of Status 0 = 2 |
| 1 | Item1 | 111 | 1 | ||
| 1 | Item2 | 112 | 0 | For Item 2 Min Status is 0 | |
| 1 | Item2 | 113 | 1 | ||
| 1 | Item2 | 114 | 2 | ||
| 1 | Item3 | 115 | 1 | For Item 3 Min Status is 1 | Total Count of Status 1 =2 |
| 1 | Item3 | 116 | 2 | ||
| 1 | Item3 | 117 | 3 | ||
| 1 | Item4 | 118 | 1 | For Item 3 Min Status is 1 | |
| 1 | Item4 | 119 | 2 | ||
| 1 | Item5 | 120 | 2 | For Item 5 Min Status is 2 | Total Count of Status 2 =1 |
| 1 | Item5 | 121 | 3 |
So final Out put should be in count and I would like to display in Bar Chart By Status Count like
Eg,
Status 0 :- 10 Count,
Status 1 :- 20 Count,
Status 2 :- 15 Count,
Status 3 :- 20 Count
From above table
Thanks In Advance
I really think these should work! :smileyhappy:
NEW Measure (Min Status) = CALCULATE ( MIN ( 'NewTable'[Item Status] ), ALLEXCEPT ( 'NewTable', 'NewTable'[Item], 'NewTable'[Id] ) )
NEW Measure (Count of Status) = CALCULATE ( COUNTA ( 'NewTable'[Item Status] ), FILTER ( 'NewTable', 'NewTable'[Item Status] = [NEW Measure (Min Status)] ), ALLEXCEPT ( 'NewTable', 'NewTable'[Item], NewTable[Id] ) )Good Luck!:smileyhappy:
3 Replies
These 2 Measures work with the sample data
Measure (Min Status) = CALCULATE ( MIN ( 'Table'[Item Status] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ) Measure (Count of Status) = CALCULATE ( COUNTA ( 'Table'[Item Status] ), FILTER ( 'Table', 'Table'[Item Status] = [Measure (Min Status)] ), ALLEXCEPT ( 'Table', 'Table'[Id] ) )And the results...
Hope this helps!
Good Luck! :smileyhappy:
Sean ,
Thanks for your quick reply and DAX calculation its near up to the solution, its really helpful but for multiple id and total count is not matched.
Here ID should be 2,3,4.... like wise and total (Measure (Count of Status )) should be match with total Item count like as below
Item 1 has Item Status 0 as a Min
Item 2 has Item Status 1 as a Min
Item 3 has Item Status 2 as a Min
Item 4 has Item Status 0 as a Min
Item 5 has Item Status 1 as a min
So
total 5 items and I
Item Status 0 = 2
Item Status 1 = 2
Item Status 2 = 1
So total should be 5 at the end
In sample data it works ut in case of multiple ID its not working. while selecting id
I think there should be minor change in calculation so here you can help out.
If possible pls help here
Thanks once again :)
Note:-1 It should be distinct count
I really think these should work! :smileyhappy:
NEW Measure (Min Status) = CALCULATE ( MIN ( 'NewTable'[Item Status] ), ALLEXCEPT ( 'NewTable', 'NewTable'[Item], 'NewTable'[Id] ) )
NEW Measure (Count of Status) = CALCULATE ( COUNTA ( 'NewTable'[Item Status] ), FILTER ( 'NewTable', 'NewTable'[Item Status] = [NEW Measure (Min Status)] ), ALLEXCEPT ( 'NewTable', 'NewTable'[Item], NewTable[Id] ) )Good Luck!:smileyhappy:
