Replace value of Matrix columns

[AKAT]

Hi community,

I have a Matrix with more than 40 columns where all have the same big value, 999.90.......

This big value is set in the database, in order to show that there is connection.

How can I replace this value with zero?

Kind Regards,

Antonios Katsikinis

[Greg_Deckler · Accepted solution]

@AKAT Are you saying you need to do this in Power Query when ingesting the data or in a Matrix visual? If in a visual, create a measure like Measure = IF(MAX('Table'[Value])=999.90,0,MAX('Table'[Value]))

[AKAT]

Hi @v-yiruan-msft 

and @Greg_Deckler 

If I will use 

New measure = IF ( [original measure] = 999.90, 0, [original measure] )

this command, it will fail because the value is 999.9000244140625...

IS there a similar command saying if value >999, then make it zero ?

Kind Regards,

Antonios Katsikinis 

[v-yiruan-msft · Accepted solution]

Hi @AKAT ,

You can update the formula of measure as below:

New measure = IF ( [original measure] >999, 0, [original measure] )

If the above one is not working for your scenario, please provide your sample pbix file (exclude sensitive data) if it is possible. Later we will provide you a suitable solution base on your sample file. Thank you.

Best Regards

[AKAT]

Hi @v-yiruan-msft,

That's perfect. Thanks.

I just created a test report, but I can see that my organisation is preventing me from sharing it outside of the company.

It is a matrix with up to 2 million lines which are consantly increasing, and 75 columns from which 28 contain data with this value.

Do you still suggest to try and converting the multiple value fields in Power Query and then unpivot ?

Kind Regards,

Antonios Katsikinis

[AKAT]

Hi @Greg_Deckler,

Only in the Matrix visual.

I need all 999.90....... values to visually change them in the Matrix to zero.

Just this. 

Any idea how ?

Is it necessary to create 40+ Measures for each Column, so to get rid of this value ?

Kind Regards,

Antonios Katsikinis 

[Greg_Deckler]

@AKAT I need a sample of your data as I am not understanding what you have exactly. Perhaps you need to unpivot your columns in your dataset>

[Greg_Deckler · Accepted solution]

@AKAT Are you saying you need to do this in Power Query when ingesting the data or in a Matrix visual? If in a visual, create a measure like Measure = IF(MAX('Table'[Value])=999.90,0,MAX('Table'[Value]))

[AKAT]

Hi @Greg_Deckler ,

Did you read my previous post ?

Is there any smart solution, or should I create 30 new calculated columns or measures ?

Please read my previous answer with pictures attached

Kind Regards,

Antonios Katsikinis

[AKAT]

Hi @Greg_Deckler ,

I attached a picture, and I hope that's clear.

 

the matrix is below

All these 999.90...(with many digits after komma) values, I wish to have them replaced on the Visual - on the matrix as zeros. Any similar command for IF value is > 990, to get to zero for the whole table?

 

Kind Regards,

Antonios Katsikinis

[v-yiruan-msft]

Hi @AKAT ,

Based on the screenshot you provided, it appears that the matrix values are from the aggregated value of multiple fields or measures. If you wanted to replace the 999.00 they contain with 0, you would need to create multiple measures like the following:

New measure = IF ( [original measure] = 999.90, 0, [original measure] )

Besides that, could you please provide these example data for these columns applied to the matrix. Perhaps we could try converting the multiple value fields applied under the Values Fields option in Power Query: unpivot to Attribute and Value columns. That way we can just create a measure just like above measure later. In any case, it depends on your model and the actual scenario to decide what approach to use.

Best Regards