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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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