CODEWITHSUNDEEP
powerbidaxpowerbi-desktopdaxstudio
RJAP
RJAP

Reputation: 49

PowerBI DAX question for groupby or other possible soution

I have a sample table like below

strong text

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?

Upvotes: 0

Views: 61

Answers (1)

AntrikshSharma
AntrikshSharma

Reputation: 661

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 )

enter image description here

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 )

enter image description here

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 )

enter image description here

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 )

enter image description here

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

enter image description here

Upvotes: 1

Related Questions