Changing values within a column in a Matrix for different DataTypes

Hello, I'm hoping to maybe get some help on this issue.

I currently have a matrix that looks like the following:

The matrix fields for the columns/rows look like the following:

Each of the rows look something like this:

As you may see, the number for LiveCount is a 1 if it's live and 0/null if it's not.

However for the units/rows where APType == "Classic" I want to put something like "N/A" and display this for all Classic Types. I was able to achieve this by replacing the values based on the APType, but the issue is that once I do that, it breaks the matrix because it can't take the SUM of different data types (aka strings or numbers) for all the subtotals. This makes sense to me to a certain degree since doing a sum of strings and numbers isn't possible. However, I'm still curious if what I'm looking for is achievable

Is there any way for me to manipulate it so that it will always take the SUM of the numbers of available (aka nulls or 1s) and just display "N/A" for all the rows/subtotal rows where applicable?

I've tried doing this with replacing the values, creating a new column/measure (but I'm unsure if I'm able to display the old column + the new one somehow) all to no avail.

Can someone help me with the above please?

Thanks!

Need Help

Show and Tell

Tutorial Requests

Anonymous
1 year ago
Hi SeungLee95 ,
Thank you danextian for the helpful response!

I tried to rereate it on my local.So that I used below dax :

LiveCountDisplayM =

VAR apType = SELECTEDVALUE(SampleData[APType])

VAR liveCount = SUM(SampleData[LiveCount])

RETURN

IF(

apType = "Classic",

"N/A",

liveCount

)

Refer the screenshot and the file for your reference.

If the solution meets your requirement,consider accepting it as solution.

Thank you.

Regards,
Pallavi.

Livecount.pbix66 KB

7 Replies

danextian
Super User
1 year ago
Hi SeungLee95

You can create another measure that returns a different value

IF ( SELECTEDVALUE ( tbl[APType] ) = "Classic", "N/A", [the measure] )

This will still return a text string so you might need to use dynamic format strings as you can directly format a text as any other data type (percentage, whole number, currency, etc).

IF ( SELECTEDVALUE ( tbl[APType] ) <> "Classic", "$#,#.00" )

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings?wt.mc_id=MVP_447534
- SeungLee95
  Microsoft Employee
  1 year ago
  Hi danextian ,
  Thank you for the reply.
  A couple of questions if I may:
  Based on your suggestion I tried the following
  
  But doesn't seem like a measure works. However, if I'm understanding correctly I could also create a new column with a similar workaround (like in my original post)
  Such as:
  However, the issue is I don't really see any of the dynamic format methods in this case.
  
  Moreover, the issue I have with this is that in the values part within my matrix I'm using this value
  
  And every row will show the subtotal. The issue with putting this new measure is that N/A does not count as a number so it cannot add each of the row's values for a subtotal and hence treats it differently. Will the dynamic formatting help with this?
  
  Sorry if I'm repeating a question or if it's what your solution is trying to do. I'm just trying to make sure I'm explaining the question correctly.
  
  Thanks!
  - SeungLee95
    Microsoft Employee
    1 year ago
    I found a quick workaround and tried the following to test things out:
    
    However, the problem is that while the N/A appears correctly for Classic Rows
    (TEST shows N/A correctly, while SR shows nothing for Classic)
    
    It doesn't show correct numbers in other APType
    
    In this case we can see SR showing added 30 or 39 for some rows. But nothing for TEST.
    The format in TEST was done dynamically as suggested too:
    
    Edit: The individual values show a $1.00 which I'm guessing is different due to how the formatting was specified.
    
    Using the formatting:
    IF ( SELECTEDVALUE ( COUDashboard[APType] ) <> "Classic", "#,0" )
    Shows the correct whole value I was looking for but it still doesn't add the subtotal for the rows.
    
    Is there any way to keep this as a number so that the subtotal for each row is just added correctly and keep the "N/A"s for classic type as just that?
Anonymous
Not applicable
1 year ago
Hi SeungLee95 ,
Thank you danextian for the helpful response!

I tried to rereate it on my local.So that I used below dax :

LiveCountDisplayM =

VAR apType = SELECTEDVALUE(SampleData[APType])

VAR liveCount = SUM(SampleData[LiveCount])

RETURN

IF(

apType = "Classic",

"N/A",

liveCount

)

Refer the screenshot and the file for your reference.

If the solution meets your requirement,consider accepting it as solution.

Thank you.

Regards,
Pallavi.

Livecount.pbix66 KB
Anonymous
Not applicable
1 year ago
Hi SeungLee95 ,
I wanted to follow up on our previous suggestions regarding the issue you are facing. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.
Thank you
Anonymous
Not applicable
1 year ago
Hi SeungLee95 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.

Thank you.
Anonymous
Not applicable
1 year ago
Hi SeungLee95 ,
I wanted to follow up on our previous suggestions regarding the issue you are facing. We would like to hear back from you to ensure we can assist you further. If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.
Thank you.