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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors