Forum Discussion
Community ChampionManipulating blanks from measure
My sample source data is following
| key |
|------|-------|---------|--------|-------|
| site | month | service | pk | Index |
| s1 | 1 | e | s1-1-e | 1 |
| s1 | 2 | e | s1-2-e | 2 |
| s1 | 3 | e | s1-3-e | 3 |
| s1 | 4 | e | s1-4-e | 4 |
| s2 | 1 | e | s2-1-e | 5 |
| s2 | 2 | e | s2-2-e | 6 |
| s2 | 3 | e | s2-3-e | 7 |
| s2 | 4 | e | s2-4-e | 8 |
| s1 | 1 | g | s1-1-g | 9 |
| s1 | 2 | g | s1-2-g | 10 |
| s1 | 3 | g | s1-3-g | 11 |
| s1 | 4 | g | s1-4-g | 12 |
| s2 | 1 | g | s2-1-g | 13 |
| s2 | 2 | g | s2-2-g | 14 |
| s2 | 3 | g | s2-3-g | 15 |
| s2 | 4 | g | s2-4-g | 16 || transaction |
|-------------|-------|---------|-------|--------|
| site | month | service | Value | pk |
| s1 | 1 | e | 2000 | s1-1-e |
| s1 | 2 | e | 3000 | s1-2-e |
| s1 | 4 | e | 1000 | s1-4-e |
| s2 | 1 | e | 5000 | s2-1-e |
| s2 | 4 | e | 1000 | s2-4-e |
| s1 | 2 | g | 3000 | s1-2-g |
| s1 | 3 | g | 4000 | s1-3-g |
| s2 | 4 | g | 5000 | s2-4-g |
| s2 | 4 | g | -1000 | s2-4-g |
I want to bring the site,month,service from key tbl in a matrix viz and show what is the max month by site-service from transaction table.
| 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 |
I wrote a measure as following
maxMoFromTransaction = CALCULATE(max(transcation[month]),ALL(transcation),VALUES(transcation[site]),VALUES(transcation[service]))
and it is doing what is supposed to do
Those blanks are legitimate as in the current filter context, it can only see those values.
But I was still wondering if it is possible to manipulate those blank values with the measure values.
https://drive.google.com/file/d/1iD6QVTma7X0fEYEX0OW9Mk1dQ_6WNZv_/view?usp=sharing
Thank you in advance.
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
4 Replies
- amitchandak
Super User
smpa01 , what do you want in place of blank ?
like
if (isblank([maxMoFromTransaction]) , [Measure], [maxMoFromTransaction])
smpa01Thanks 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
