Group by Dax functions

[Anonymous]

Hi,

I have data that looks like this: 

Brown colored are input columns

Blue colored are output column

Anchor Config ID	Target Config ID	Dealer (of target config ID)	Enabled for requested dealer	# of dealers target config mapped to
101	101	D140	Yes	1
101	102	H200	Yes	2
101	102	D140	Yes	2
101	103	Z050	No	1
102	101	N050	Yes	1
102	102	N050	Yes	1
102	103	D140	No	1
103	101	E070	No	1
103	102	E300	No	1
103	103	N150	Yes	1

Logic

1 Enabled for requested dealer:

In the above example, Anchor Config is a filter.

If (Dealer of Anchor Config = Dealer of Target Config) then Yes else No

Eg dealer of 102 is D140 and so is the dealer of 101 so Enabled for requested dealer for 102=Yes

Caveat: If target config ID has  > 2 dealers with atleast 1 Yes, then all those rows for target config id will become Yes

2 # of dealers target config mapped to

If target config ID occurs more than once then count (rows)

Eg there are 2 dealers for 102 Target Config or 2 rows of repeated values, so all 102 Target Config IDs values will become 2 for # of dealers target config mapped to

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Enabled for requested dealer = 
VAR CurrentAnchor = 'Table'[Anchor Config ID]
VAR CurrentAnchorDealer =
    CALCULATE ( MAX ( 'Table'[Dealer] ), CurrentAnchor = 'Table'[Target Config ID] )
VAR CountOfTargetDealer =
    CALCULATE (
        COUNT ( 'Table'[Target Config ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Anchor Config ID], 'Table'[Target Config ID] )
    )
RETURN
    IF (
        CurrentAnchorDealer <> BLANK (),
        "Yes",
        IF ( CountOfTargetDealer >= 2 && CurrentAnchorDealer = BLANK (), 
        "Yes", 
        "No" )
    )

# of dealers target config mapped to =
CALCULATE (
    COUNT ( 'Table'[Target Config ID] ),
    ALLEXCEPT ( 'Table', 'Table'[Anchor Config ID], 'Table'[Target Config ID] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.