topic Filter table visual to show single row per value, according to other values in the source table. in DAX Commands and Tips

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

HM615 — Tue, 03 Sep 2024 15:21:04 GMT

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!

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

BabyYoda — Tue, 03 Sep 2024 15:34:42 GMT

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)

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

Jihwan_Kim — Wed, 04 Sep 2024 04:17:50 GMT

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