Manipulating blanks from measure

[smpa01]

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.

[amitchandak]

@smpa01 , what do you want in place of blank ?

like

if (isblank([maxMoFromTransaction]) , [Measure], [maxMoFromTransaction])

[smpa01]

Thanks @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                    |

[v-yiruan-msft · Accepted solution]

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]

@v-yiruan-msft  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.