Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace value of Matrix columns

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...
Interesting Links
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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