Forum Discussion
Super UserDAX measure help needed
Hi Team,
I have a below input table :
| YearMonth | Technology | AvailableQuantity |
| 202311 | TV | 12 |
| 202311 | Laptop | 24 |
| 202311 | Speaker | 3 |
| 202312 | TV | 4 |
| 202312 | Laptop | 5 |
| 202401 | TV | 1 |
| 202401 | Laptop | 23 |
| 202401 | Speaker | 4 |
| 202402 | Laptop | 67 |
| 202403 | Speaker | 89 |
| 202403 | Laptop | 43 |
| 202404 | TV | 7 |
| 202405 | TV | 8 |
We have a slicer for yearmonth column and below is the scenario :
If I select year 202401 from the slicer , then below is the calculation and the expected output in a matrix visualization :
Example:
when 202401 is selected :
then the sum of available quantity is 28, so it will appear like below :
Now for other yearmonth combination, We can see blank values but we need desired output table (which is highlighted as green table in the snapshot) as the final output.
Logic : if the same technology is present in other yearmonth combination then the value should get repeated. In this case, TV,Laptop,speaker is repeated in 202311 then we should get 28 in the desiredoutput column.
Similarly, for 202404 - only TV is matching then the desiredoutput column will show 1.
Appreciate your help here!
Thanks,
Ankita
AnkitaaMishra You need a disconnected slicer table and this measure. See PBIX attached below signature.
Measure = VAR __YearMonth = MAX( 'Slicer'[YearMonth] ) VAR __Technologies = DISTINCT( SELECTCOLUMNS( 'Table', "__Technology", [Technology] ) ) VAR __Table = FILTER( ALL( 'Table' ), [YearMonth] = __YearMonth ) VAR __Result = SUMX( FILTER( __Table, [Technology] IN __Technologies ), [AvailableQuantity ] ) RETURN __Result
1 Reply
- Greg_Deckler
Community Champion
AnkitaaMishra You need a disconnected slicer table and this measure. See PBIX attached below signature.
Measure = VAR __YearMonth = MAX( 'Slicer'[YearMonth] ) VAR __Technologies = DISTINCT( SELECTCOLUMNS( 'Table', "__Technology", [Technology] ) ) VAR __Table = FILTER( ALL( 'Table' ), [YearMonth] = __YearMonth ) VAR __Result = SUMX( FILTER( __Table, [Technology] IN __Technologies ), [AvailableQuantity ] ) RETURN __Result