Forum Discussion
Group by Dax functions
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
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.
1 Reply
- Icey
Community Support
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.