Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
smpa01
Super User
Super User

Manipulating 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

 

cap8.PNG

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.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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])

yingyinr_0-1631867383312.png

Best Regards

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

 

like

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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                    |
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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])

yingyinr_0-1631867383312.png

Best Regards

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.