Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Manipulating blanks from measure

My sample source data is following | key | |------|-------|---------|--------|-------| | site | month | service | pk | Index | | s1 | 1 | e | s1-...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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.