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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Learn2Share
Frequent Visitor

How to replace column values using DAX measure?

Hi,

 

Is it possible to change column values using dax measure (no option to modify power query/tranform data option, no option to create a calculated column as Powerbi is connected to AAS live connection)

 

Such that if I pull an attribute "Sno" on table visual &  this measure I should get output as below?

 

SnoExisting Field ValuesMeasure Output Values
11P1
22P2
33P3
4BLANKOther

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Learn2Share,

 

Try this measure:

 

Output Value = 
SWITCH (
    MAX ( Table1[Existing Field Values] ),
    1, "P1",
    2, "P2",
    3, "P3",
    BLANK (), "Other"
)

 

DataInsights_0-1674655588255.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Learn2Share
Frequent Visitor

thank you @DataInsights , this works!

DataInsights
Super User
Super User

@Learn2Share,

 

Try this measure:

 

Output Value = 
SWITCH (
    MAX ( Table1[Existing Field Values] ),
    1, "P1",
    2, "P2",
    3, "P3",
    BLANK (), "Other"
)

 

DataInsights_0-1674655588255.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I have a related question. I'm currently using a column name called ItemName in tables and charts, but would instead like to use a measure value ShortName:

 

I would like to replace the ItemName value with ShortName:

ItemNameShortName
Product Group Alpha

Alpha

Product Group BetaBeta
Product Group CharlieCharlie

 

Here's what I tried:

 

ShortName =
    SWITCH(
        MAX('DimCatalogItemServiceSDD'[ItemName]),
        "Product Group Alpha", "Alpha",
        "Product Group Beta", "Beta",
        "Product Group Charlie", "Charlie",
        "Nada"
    )
 
When I add the measure to a blank table visual, all I get is "Nada".
 
Any help would be appreciated!

@JimBoyce,

 

The simplest approach is to create a custom column (Power Query) or calculated column (DAX). If neither of these are options, temporarily change your measure to return the first SWITCH argument:

 

MAX('DimCatalogItemServiceSDD'[ItemName])

 

It appears there is a value in ItemName that is not in your SWITCH expression (causing it to return "Nada").





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I appreciate the response. I get what you're saying, but I'm not sure how to modify the measure to return the first result. Can you provide what the full measure would look like based on the above example? Thanks!

@JimBoyce,

 

Something like this:

 

Test Measure = MAX ( 'DimCatalogItemServiceSDD'[ItemName] )

 

You mentioned that you added your measure to a blank table visual. That means there's no filter context (other than external filters) so the max value of ItemName in the entire column (considering external filters) is what gets returned. It's better to create a lookup table than to use DAX.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.