Filter table visual to show single row per value, according to other values in the source table.

[HM615]

In my source data, there are multiple rows per ID number. I have a simple table visual showing certain values that correspond to that ID number. I want to filter that visual so each ID number only appears once, according to specific criteria. I am pasting dummy sample data below to make this clearer. What I am looking for is the column B value for each ID in column A - however, I only want one single B value for each ID in column A, although my source data has multiple rows with multiple values. If column C is UF, that is the value I want. If there is no UF for a particular ID, then I want the max value that is in B. I have no idea how to do it! 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: =
VAR _partitionuf =
    FILTER (
        WINDOW (
            1,
            ABS,
            -1,
            ABS,
            ALL ( data ),
            ORDERBY ( data[value], DESC ),
            ,
            PARTITIONBY ( data[id] ),
            MATCHBY ( data[id], data[value], data[condition] )
        ),
        data[condition] = "uf"
    )
VAR _partitionall =
    WINDOW (
        1,
        ABS,
        1,
        ABS,
        ALL ( data ),
        ORDERBY ( data[value], DESC ),
        ,
        PARTITIONBY ( data[id] ),
        MATCHBY ( data[id], data[value], data[condition] )
    )
RETURN
    IF (
        COUNTROWS ( _partitionuf ) >= 1,
        MAXX ( _partitionuf, data[value] ),
        MAXX ( _partitionall, data[value] )
    )

[BabyYoda]

Try this if you need a measure: 

Cleaned Column B Measure = Var ColA = SELECTEDVALUE(SampleTable[Column A])

Var HasUF = MAXX(FILTER(SampleTable, SampleTable[Column A]=ColA&&SampleTable[Column C]="UF"), SampleTable[Column B])

Var NoUF = MAXX(FILTER(SampleTable, SampleTable[Column A]=ColA), SampleTable[Column B])

 RETURN SWITCH(TRUE(), ISBLANK(HasUF), NoUF, HasUF)

or if you need a calculated column try this

Cleaned Column B = Var ColA = SampleTable[Column A]

Var HasUF = MAXX(FILTER(SampleTable, SampleTable[Column A]=ColA&&SampleTable[Column C]="UF"), SampleTable[Column B])

Var NoUF = MAXX(FILTER(SampleTable, SampleTable[Column A]=ColA), SampleTable[Column B])

 RETURN SWITCH(TRUE(), ISBLANK(HasUF), NoUF, HasUF)