How to substitute blank value to text value in Matrix? (Calculated measures)

[Anonymous]

Hello everyone!

I have some troubles with DAX...

I took the dataset (see below).

Description: There are a list of customers which make purchases through various Services. I  calculate maketshare of Service "RTS-Tender"(see the slicer) by sum of purchases in the Martrix, but in barchart I  show markershare not only "RTS-Tender"`s share but share  of the others Services by selected  customer(I can select it in matrix).  As you can see it works by changing relationships between visual elements. 

BUT I want to see the incription "Another Service" instead BLANK VALUES in the Matrix that another service provides the purchases for customers. Something like that:

Note:

As you understand in a  month there is no purchaces at all. It would be perfect if  in this case the inscription was like "No purchases" for example... 

Here is DAX formulas of calculated measures:

Measure SUM = SUM('Dataset'[SUM])

Measure Total SUM = CALCULATE('Dataset'[Measure SUM];ALL('Dataset'[ServiceName]))

MarketShare of ServiceName by Customer = DIVIDE([Measure SUM];[Measure Total SUM])

How should I change DAX formula to do it? Is it real?

I really need your help otherwise the boss will execute me.

Thank you very much in advance!!!

[Anonymous]

@webportal Hello! I saw your replies in other topics, I think that you could help me. Please, could you look through my problem?

[webportal]

@Anonymous if you want to display text in a matrix, that should be ok with a measure.

Something like:

Measure = IF(ISBLANK(MarketShare); "SomeText"; MarketShare)

However, in a chart, I don't think it will work because to feed the chart you must use values.

Hope this helps 🙂

[Anonymous]

Thank you ver much for your reply!

@webportalUnfortunately , It works but incorrectly...  The most blank values still blank and  sometimes text value  is displayed incorretly...

When I look at srceenshot I can`t  catch logic of displaying blank and not blank values ((((

[v-ljerr-msft]

Hi @Anonymous,

Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[Anonymous]

Thank for your reply!

@v-ljerr-msft  Download link : https://1drv.ms/u/s!AsB3svvakW47iENfmYrnH5o7W4uQ

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

After looking into your shared pbix file, I find that you may need to add a new calculate table which contains the individual 'Customer' value first.

Customer = DISTINCT(SELECTCOLUMNS('Dataset',"Customer",'Dataset'[Customer]))

And create a relationship between the new table and your original table.

Then use the 'Customer' column from the new created table as Rows in the Matrix, then the measure should work as expected.

Here is the modified pbix file for your reference. 

Regards

[Anonymous]

@v-ljerr-msft Thank you very much, my friend! Althought it`s not the direct solution, but you pushed me to  my problem`s full-solution . That`s nice idea to create a new table!