PowerBI DAX question for groupby or other possible soution

Question

I have a sample table like below

Any same ID that have column M Name with A (starting as "Ann") and B then I want to have desired solution as A,B

I then think about count any words "Ann" and B as 1, so when any same ID has column Calculation as 2 or more than 2 then give me "A,B". Otherwise, just return the original name in column "M Name"

Could someone please help with PowerBI DAX or have any recommendation?

I would think it'll be better to have calculated column because I can double check — RJAP, Apr 15 '21 at 01:14

score 1 · Answer 1 · answered Apr 16 '21 at 07:30

There are mulitple ways of doing this:

Calculated column using FILTER:

Column 1 = 
VAR CurrentID = rjap[ID]
VAR CurrentName = rjap[M Name]
VAR RowsWithSameID =
    FILTER ( rjap, rjap[ID] = CurrentID )
VAR Result =
    FILTER (
        RowsWithSameID,
        LEFT ( rjap[M Name], 3 ) = "Ann"
            || LEFT ( rjap[M Name], 1 ) = "B"
    )
RETURN
    IF ( COUNTROWS ( Result ) >= 2, "A, B", CurrentName )

Calculated column using CALCULATETABLE

Column 2 =
VAR CurrentID = rjap[ID]
VAR CurrentName = rjap[M Name]
VAR Result =
    CALCULATETABLE (
        rjap,
        LEFT ( rjap[M Name], 3 ) = "Ann"
            || LEFT ( rjap[M Name], 1 ) = "B",
        rjap[ID] = CurrentID,
        REMOVEFILTERS ( rjap )
    )
RETURN
    IF ( COUNTROWS ( Result ) >= 2, "A, B", CurrentName )

Measure using FILTER:

Measure 1 = 
VAR CurrentID = SELECTEDVALUE ( rjap[ID] )
VAR CurrentName = SELECTEDVALUE ( rjap[M Name] )
VAR RowsWithSameID =
    FILTER ( 
        ALLSELECTED ( rjap ), 
        rjap[ID] = CurrentID
    )
VAR Result =
    FILTER (
        RowsWithSameID,
        LEFT ( rjap[M Name], 3 ) = "Ann"
            || LEFT ( rjap[M Name], 1 ) = "B"
    )
RETURN
    IF ( COUNTROWS ( Result ) >= 2, "A, B", CurrentName )

Measure using CALCULATETABLE

Measure 2 = 
VAR CurrentID = SELECTEDVALUE ( rjap[ID] )
VAR CurrentName = SELECTEDVALUE ( rjap[M Name] )
VAR Result =
    CALCULATETABLE ( 
        rjap,
        LEFT ( rjap[M Name], 3 ) = "Ann"
            || LEFT ( rjap[M Name], 1 ) = "B",
        rjap[ID] = CurrentID,
        ALLSELECTED ( rjap ) 
    )
RETURN
    IF ( COUNTROWS ( Result ) >= 2, "A, B", CurrentName )

I have used ALLSELECTED so if in case you have a selection over a slicer you will only see result for the selected IDs

PowerBI DAX question for groupby or other possible soution

1 Answers1