Forum Discussion
Community ChampionManipulating blanks from measure
Hi smpa01 ,
I have updated your sample pbix file(see attachment), please check whether that is what you want.
1. Update the formula of your measure [maxMoFromTransaction] as below
maxMoFromTransaction = CALCULATE ( MAX ( transcation[month] ), FILTER ( ALLSELECTED ( 'transcation' ), 'transcation'[site] = SELECTEDVALUE ( 'key'[site] ) && 'transcation'[service] = SELECTEDVALUE ( 'key'[service] ) ) )2. Create another new measure and put it on the matrix to replace the measure [maxMoFromTransaction]
Measure 4 = SUMX(GROUPBY('key','key'[site],'key'[service]),[maxMoFromTransaction])Best Regards
Community ChampionThanks amitchandak
My desired output is following
| site | month | service | maxMofromTransaction |
|------|-------|---------|----------------------|
| s1 | 1 | e | 4 |
| s1 | 2 | e | 4 |
| s1 | 3 | e | 4 |
| s1 | 4 | e | 4 |
| s2 | 1 | e | 4 |
| s2 | 2 | e | 4 |
| s2 | 3 | e | 4 |
| s2 | 4 | e | 4 |
| s1 | 1 | g | 3 |
| s1 | 2 | g | 3 |
| s1 | 3 | g | 3 |
| s1 | 4 | g | 3 |
| s2 | 1 | g | 4 |
| s2 | 2 | g | 4 |
| s2 | 3 | g | 4 |
| s2 | 4 | g | 4 |Hi smpa01 ,
I have updated your sample pbix file(see attachment), please check whether that is what you want.
1. Update the formula of your measure [maxMoFromTransaction] as below
maxMoFromTransaction =
CALCULATE (
MAX ( transcation[month] ),
FILTER (
ALLSELECTED ( 'transcation' ),
'transcation'[site] = SELECTEDVALUE ( 'key'[site] )
&& 'transcation'[service] = SELECTEDVALUE ( 'key'[service] )
)
)2. Create another new measure and put it on the matrix to replace the measure [maxMoFromTransaction]
Measure 4 = SUMX(GROUPBY('key','key'[site],'key'[service]),[maxMoFromTransaction])
Best Regards
- smpa01
Anonymous many thanks. ALLSELECTED is where the magic hapens and at the past I had severe perfomance issues with ALLSELECTED for large tables. I am not sure if I can utilize this in prod. However, many thanks for looking into this and providing a solution. If ALLSELECTED is the only way to achieve the end result, I may have to model the data differently so that I can avoid.