Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- DAX measure to evaluate for all conditional instan...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DAX measure to evaluate for all conditional instances of another measure in expanded table

09-10-2021
11:18 AM

My source data is following (pbix is attached)

and keyTbl has 1:1 relationship with condition tbl based on pk.

In both tables pk is based on site-month-service combination. keyTbl contains all possible combinations of site-month-service combination where as condition tbl is a fact tbl and it might or might not record all the transaction lines for site-month-service.

I am building a viz where I bring site, month, service in a matrix viz from key Tbl and bring the corresponding condition from the condition table with the following measure

`maxCondition = max('condition'[condition])`

When I drop this measure to the viz, it looks like this on the expanded table

Now, I want to write a another measure to return the site number for where maxCondition is *only* X and Y and no other condition by

My desired result is following

```
| result |
|--------|---------|-------|--------------|---------|
| site | service | month | maxCondition | _siteXY |
| 1000 | e | 1 | X | |
| 1000 | e | 2 | Y | |
| 1000 | e | 3 | | |
| 1000 | e | 4 | | |
| 1000 | g | 1 | X | 1000 |
| 1000 | g | 2 | Y | 1000 |
| 1000 | g | 3 | X | 1000 |
| 1000 | g | 4 | Y | 1000 |
| 1001 | e | 1 | | |
| 1001 | e | 2 | | |
| 1001 | e | 3 | | |
| 1001 | e | 4 | | |
| 1001 | g | 1 | A | |
| 1001 | g | 2 | B | |
| 1001 | g | 3 | | |
| 1001 | g | 4 | | |
| 1002 | e | 1 | A | |
| 1002 | e | 2 | B | |
| 1002 | e | 3 | | |
| 1002 | e | 4 | | |
| 1002 | g | 1 | | |
| 1002 | g | 2 | | |
| 1002 | g | 3 | | |
| 1002 | g | 4 | | |
| 1003 | e | 1 | E | |
| 1003 | e | 2 | X | |
| 1003 | e | 3 | | |
| 1003 | e | 4 | | |
| 1003 | g | 1 | X | 1003 |
| 1003 | g | 2 | Y | 1003 |
| 1003 | g | 3 | X | 1003 |
| 1003 | g | 4 | Y | 1003 |
| 1004 | e | 1 | | |
| 1004 | e | 2 | | |
| 1004 | e | 3 | | |
| 1004 | e | 4 | | |
| 1004 | g | 1 | A | |
| 1004 | g | 2 | B | |
| 1004 | g | 3 | A | |
| 1004 | g | 4 | B | |
| 1005 | e | 1 | X | 1005 |
| 1005 | e | 2 | Y | 1005 |
| 1005 | e | 3 | X | 1005 |
| 1005 | e | 4 | Y | 1005 |
| 1005 | g | 1 | | |
| 1005 | g | 2 | | |
| 1005 | g | 3 | | |
| 1005 | g | 4 | | |
```

So far, I tried this

```
_siteXY =
VAR _1 = ADDCOLUMNS(keyTbl,"cond",[maxCondition])
VAR _2 = FILTER(_1,[cond] in {"X","Y"})
VAR _20 = SUMMARIZE(_2,[site],[service])
VAR _3 = FILTER(_1,NOT [cond] in {"X","Y"})
VAR _30 = SUMMARIZE(_3,[site],[service])
VAR _4 = EXCEPT(_20,_30)
VAR _5 = CALCULATETABLE(keyTbl,_4)
VAR _6 = CALCULATE(MAXX(FILTER(_5,[pk]=max(keyTbl[pk])),[pk]),ALL(keyTbl[pk]))
RETURN _6
```

I have no ide why the above would not work, whre VAR _5 contains a table with all the required combinations and why VAR _6 can't filter it.

Thank you in advance.

https://drive.google.com/file/d/1xCbF6Wv9CiLYzDECR_2FWcTRviHnYGSC/view?usp=sharing

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
04:53 PM

OK @smpa01 lets give this a try.

```
maxCondition =
VAR _Months = 4
VAR _SiteService =
ADDCOLUMNS (
SUMMARIZE ( 'condition', 'condition'[site], 'condition'[service] ),
"@Rows",
CALCULATE (
COUNTROWS ( 'condition' ),
'condition'[condition] IN { "x", "y" },
REMOVEFILTERS ( keyTbl[month] )
)
)
VAR _RowsToCalc =
FILTER ( _SiteService, [@Rows] = _Months )
RETURN
CALCULATE ( MAX ( 'condition'[condition] ), _RowsToCalc )
```

```
SiteXY =
VAR _Months = 4
VAR _SiteService =
ADDCOLUMNS (
SUMMARIZE ( 'condition', 'condition'[site], 'condition'[service] ),
"@Rows",
CALCULATE (
COUNTROWS ( 'condition' ),
'condition'[condition] IN { "x", "y" },
REMOVEFILTERS ( keyTbl[month] )
)
)
VAR _RowsToCalc =
FILTER ( _SiteService, [@Rows] = _Months )
RETURN
CALCULATE ( MAX ( 'condition'[site] ), _RowsToCalc )
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2021
09:32 PM

@jdbuchanan71 many thanks for this. You solution is simple and very elegant and it works awesome.

But if I have variable number of months both in keyTbl and condition Tbl, it would not work, cause we are hardcoding the following portion there

`VAR _Months = 4`

Revised dataset with 5 rows for 1000-g in both key and condition Tbl

```
| keyTbl |
|--------|-------|----------|---------|
| site | month | pk | service |
| 1000 | 1 | 1000-1-e | e |
| 1000 | 2 | 1000-2-e | e |
| 1000 | 3 | 1000-3-e | e |
| 1000 | 4 | 1000-4-e | e |
| 1001 | 1 | 1001-1-e | e |
| 1001 | 2 | 1001-2-e | e |
| 1001 | 3 | 1001-3-e | e |
| 1001 | 4 | 1001-4-e | e |
| 1002 | 1 | 1002-1-e | e |
| 1002 | 2 | 1002-2-e | e |
| 1002 | 3 | 1002-3-e | e |
| 1002 | 4 | 1002-4-e | e |
| 1003 | 1 | 1003-1-e | e |
| 1003 | 2 | 1003-2-e | e |
| 1003 | 3 | 1003-3-e | e |
| 1003 | 4 | 1003-4-e | e |
| 1004 | 1 | 1004-1-e | e |
| 1004 | 2 | 1004-2-e | e |
| 1004 | 3 | 1004-3-e | e |
| 1004 | 4 | 1004-4-e | e |
| 1005 | 1 | 1005-1-e | e |
| 1005 | 2 | 1005-2-e | e |
| 1005 | 3 | 1005-3-e | e |
| 1005 | 4 | 1005-4-e | e |
| 1000 | 1 | 1000-1-g | g |
| 1000 | 2 | 1000-2-g | g |
| 1000 | 3 | 1000-3-g | g |
| 1000 | 4 | 1000-4-g | g |
| 1000 | 5 | 1000-5-g | g |
| 1001 | 1 | 1001-1-g | g |
| 1001 | 2 | 1001-2-g | g |
| 1001 | 3 | 1001-3-g | g |
| 1001 | 4 | 1001-4-g | g |
| 1002 | 1 | 1002-1-g | g |
| 1002 | 2 | 1002-2-g | g |
| 1002 | 3 | 1002-3-g | g |
| 1002 | 4 | 1002-4-g | g |
| 1003 | 1 | 1003-1-g | g |
| 1003 | 2 | 1003-2-g | g |
| 1003 | 3 | 1003-3-g | g |
| 1003 | 4 | 1003-4-g | g |
| 1004 | 1 | 1004-1-g | g |
| 1004 | 2 | 1004-2-g | g |
| 1004 | 3 | 1004-3-g | g |
| 1004 | 4 | 1004-4-g | g |
| 1005 | 1 | 1005-1-g | g |
| 1005 | 2 | 1005-2-g | g |
| 1005 | 3 | 1005-3-g | g |
| 1005 | 4 | 1005-4-g | g |
```

```
| condition |
|-----------|-----------|------|-------|---------|
| pk | condition | site | month | service |
| 1000-1-e | X | 1000 | 1 | e |
| 1000-2-e | Y | 1000 | 2 | e |
| 1002-1-e | A | 1002 | 1 | e |
| 1002-2-e | B | 1002 | 2 | e |
| 1003-1-e | E | 1003 | 1 | e |
| 1003-2-e | X | 1003 | 2 | e |
| 1005-1-e | X | 1005 | 1 | e |
| 1005-2-e | Y | 1005 | 2 | e |
| 1005-3-e | X | 1005 | 3 | e |
| 1005-4-e | Y | 1005 | 4 | e |
| 1000-1-g | X | 1000 | 1 | g |
| 1000-2-g | Y | 1000 | 2 | g |
| 1000-3-g | X | 1000 | 3 | g |
| 1000-4-g | Y | 1000 | 4 | g |
| 1000-5-g | X | 1000 | 5 | g |
| 1001-1-g | A | 1001 | 1 | g |
| 1001-2-g | B | 1001 | 2 | g |
| 1004-1-g | A | 1004 | 1 | g |
| 1004-2-g | B | 1004 | 2 | g |
| 1004-3-g | A | 1004 | 3 | g |
| 1004-4-g | B | 1004 | 4 | g |
| 1003-1-g | X | 1003 | 1 | g |
| 1003-2-g | Y | 1003 | 2 | g |
| 1003-3-g | X | 1003 | 3 | g |
| 1003-4-g | Y | 1003 | 4 | g |
```

MD to table converter - https://tableconvert.com/

If I have variable rows, a complete dynamic solution is following

```
_XYCount =
CALCULATE (
COUNT ( 'condition'[condition] ),
'condition'[condition] IN { "X", "Y" },
ALLEXCEPT ( keyTbl, keyTbl[site], keyTbl[service] )
)
_siteServiceCount =
CALCULATE (
COUNT ( keyTbl[site] ),
ALLEXCEPT ( keyTbl, keyTbl[service], keyTbl[site] )
)
maxSite =
CALCULATE ( MAX ( keyTbl[site] ) )
conditionalSite =
IF ( [_siteServiceCount] = [_XYCount], [maxSite] )
conditionalSite =
IF ( [_siteServiceCount] = [_XYCount], [maxSite] )
```

Putting everything together

```
Combined =
VAR _XYCount =
CALCULATE (
COUNT ( 'condition'[condition] ),
'condition'[condition] IN { "X", "Y" },
ALLEXCEPT ( keyTbl, keyTbl[site], keyTbl[service] )
)
VAR _siteServiceCount =
CALCULATE (
COUNT ( keyTbl[site] ),
ALLEXCEPT ( keyTbl, keyTbl[service], keyTbl[site] )
)
VAR _maxSite =
CALCULATE ( MAX ( keyTbl[site] ) )
VAR _conditionalSite =
IF ( _siteServiceCount = _XYCount, _maxSite )
RETURN
_conditionalSite
```

And yes, the concept was actually taken from a page of SQL @CNENFRNL

If you can imagine @t1 as the expanded table in DAX viz and cond is what happens when you drop the following measure

maxCondition = max('condition'[condition])

This is how you would actually solve this problem in TSQL and I wrote my dynamic DAX measure (combined) *with exactly same concept*.

For SQL and DAX enthusiasts

```
DECLARE @t1 AS TABLE ( sn int,site varchar(max),cond varchar(max),val int )
INSERT INTO @t1
SELECT * FROM
(VALUES
(1, 'site1', 'X', 100),(2, 'site1', 'Y', 200), (3, 'site1', 'Z', 300),(4, 'site1', 'X', 100 ),
(1, 'site2', 'X', 100),(2, 'site2', 'Y', 200),(3, 'site2', 'Z', 300),
(1, 'site3', 'X', 100),(2, 'site3', 'P', 200),(3, 'site3', 'Q', 300),
(1, 'site4', 'A', 100),(2, 'site4', 'Y', 200),(3, 'site4', 'Q', 300),(1, 'site5', 'E', 100),
(1, 'site5', 'E', 1000),(2, 'site5', 'F', 200),(3, 'site5', 'G', 300)
) t (a, b, c, d)
select *,
Iif(Sum(case when cond in ('X', 'Y', 'Z') then 1 end) over (partition by site) = Count(*) over (partition by site), 1, 0
) as test
from @t1
```

@jdbuchanan71 can't thank you enough for your time.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

19 REPLIES 19

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
04:53 PM

OK @smpa01 lets give this a try.

```
maxCondition =
VAR _Months = 4
VAR _SiteService =
ADDCOLUMNS (
SUMMARIZE ( 'condition', 'condition'[site], 'condition'[service] ),
"@Rows",
CALCULATE (
COUNTROWS ( 'condition' ),
'condition'[condition] IN { "x", "y" },
REMOVEFILTERS ( keyTbl[month] )
)
)
VAR _RowsToCalc =
FILTER ( _SiteService, [@Rows] = _Months )
RETURN
CALCULATE ( MAX ( 'condition'[condition] ), _RowsToCalc )
```

```
SiteXY =
VAR _Months = 4
VAR _SiteService =
ADDCOLUMNS (
SUMMARIZE ( 'condition', 'condition'[site], 'condition'[service] ),
"@Rows",
CALCULATE (
COUNTROWS ( 'condition' ),
'condition'[condition] IN { "x", "y" },
REMOVEFILTERS ( keyTbl[month] )
)
)
VAR _RowsToCalc =
FILTER ( _SiteService, [@Rows] = _Months )
RETURN
CALCULATE ( MAX ( 'condition'[site] ), _RowsToCalc )
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2021
09:32 PM

@jdbuchanan71 many thanks for this. You solution is simple and very elegant and it works awesome.

But if I have variable number of months both in keyTbl and condition Tbl, it would not work, cause we are hardcoding the following portion there

`VAR _Months = 4`

Revised dataset with 5 rows for 1000-g in both key and condition Tbl

```
| keyTbl |
|--------|-------|----------|---------|
| site | month | pk | service |
| 1000 | 1 | 1000-1-e | e |
| 1000 | 2 | 1000-2-e | e |
| 1000 | 3 | 1000-3-e | e |
| 1000 | 4 | 1000-4-e | e |
| 1001 | 1 | 1001-1-e | e |
| 1001 | 2 | 1001-2-e | e |
| 1001 | 3 | 1001-3-e | e |
| 1001 | 4 | 1001-4-e | e |
| 1002 | 1 | 1002-1-e | e |
| 1002 | 2 | 1002-2-e | e |
| 1002 | 3 | 1002-3-e | e |
| 1002 | 4 | 1002-4-e | e |
| 1003 | 1 | 1003-1-e | e |
| 1003 | 2 | 1003-2-e | e |
| 1003 | 3 | 1003-3-e | e |
| 1003 | 4 | 1003-4-e | e |
| 1004 | 1 | 1004-1-e | e |
| 1004 | 2 | 1004-2-e | e |
| 1004 | 3 | 1004-3-e | e |
| 1004 | 4 | 1004-4-e | e |
| 1005 | 1 | 1005-1-e | e |
| 1005 | 2 | 1005-2-e | e |
| 1005 | 3 | 1005-3-e | e |
| 1005 | 4 | 1005-4-e | e |
| 1000 | 1 | 1000-1-g | g |
| 1000 | 2 | 1000-2-g | g |
| 1000 | 3 | 1000-3-g | g |
| 1000 | 4 | 1000-4-g | g |
| 1000 | 5 | 1000-5-g | g |
| 1001 | 1 | 1001-1-g | g |
| 1001 | 2 | 1001-2-g | g |
| 1001 | 3 | 1001-3-g | g |
| 1001 | 4 | 1001-4-g | g |
| 1002 | 1 | 1002-1-g | g |
| 1002 | 2 | 1002-2-g | g |
| 1002 | 3 | 1002-3-g | g |
| 1002 | 4 | 1002-4-g | g |
| 1003 | 1 | 1003-1-g | g |
| 1003 | 2 | 1003-2-g | g |
| 1003 | 3 | 1003-3-g | g |
| 1003 | 4 | 1003-4-g | g |
| 1004 | 1 | 1004-1-g | g |
| 1004 | 2 | 1004-2-g | g |
| 1004 | 3 | 1004-3-g | g |
| 1004 | 4 | 1004-4-g | g |
| 1005 | 1 | 1005-1-g | g |
| 1005 | 2 | 1005-2-g | g |
| 1005 | 3 | 1005-3-g | g |
| 1005 | 4 | 1005-4-g | g |
```

```
| condition |
|-----------|-----------|------|-------|---------|
| pk | condition | site | month | service |
| 1000-1-e | X | 1000 | 1 | e |
| 1000-2-e | Y | 1000 | 2 | e |
| 1002-1-e | A | 1002 | 1 | e |
| 1002-2-e | B | 1002 | 2 | e |
| 1003-1-e | E | 1003 | 1 | e |
| 1003-2-e | X | 1003 | 2 | e |
| 1005-1-e | X | 1005 | 1 | e |
| 1005-2-e | Y | 1005 | 2 | e |
| 1005-3-e | X | 1005 | 3 | e |
| 1005-4-e | Y | 1005 | 4 | e |
| 1000-1-g | X | 1000 | 1 | g |
| 1000-2-g | Y | 1000 | 2 | g |
| 1000-3-g | X | 1000 | 3 | g |
| 1000-4-g | Y | 1000 | 4 | g |
| 1000-5-g | X | 1000 | 5 | g |
| 1001-1-g | A | 1001 | 1 | g |
| 1001-2-g | B | 1001 | 2 | g |
| 1004-1-g | A | 1004 | 1 | g |
| 1004-2-g | B | 1004 | 2 | g |
| 1004-3-g | A | 1004 | 3 | g |
| 1004-4-g | B | 1004 | 4 | g |
| 1003-1-g | X | 1003 | 1 | g |
| 1003-2-g | Y | 1003 | 2 | g |
| 1003-3-g | X | 1003 | 3 | g |
| 1003-4-g | Y | 1003 | 4 | g |
```

MD to table converter - https://tableconvert.com/

If I have variable rows, a complete dynamic solution is following

```
_XYCount =
CALCULATE (
COUNT ( 'condition'[condition] ),
'condition'[condition] IN { "X", "Y" },
ALLEXCEPT ( keyTbl, keyTbl[site], keyTbl[service] )
)
_siteServiceCount =
CALCULATE (
COUNT ( keyTbl[site] ),
ALLEXCEPT ( keyTbl, keyTbl[service], keyTbl[site] )
)
maxSite =
CALCULATE ( MAX ( keyTbl[site] ) )
conditionalSite =
IF ( [_siteServiceCount] = [_XYCount], [maxSite] )
conditionalSite =
IF ( [_siteServiceCount] = [_XYCount], [maxSite] )
```

Putting everything together

```
Combined =
VAR _XYCount =
CALCULATE (
COUNT ( 'condition'[condition] ),
'condition'[condition] IN { "X", "Y" },
ALLEXCEPT ( keyTbl, keyTbl[site], keyTbl[service] )
)
VAR _siteServiceCount =
CALCULATE (
COUNT ( keyTbl[site] ),
ALLEXCEPT ( keyTbl, keyTbl[service], keyTbl[site] )
)
VAR _maxSite =
CALCULATE ( MAX ( keyTbl[site] ) )
VAR _conditionalSite =
IF ( _siteServiceCount = _XYCount, _maxSite )
RETURN
_conditionalSite
```

And yes, the concept was actually taken from a page of SQL @CNENFRNL

If you can imagine @t1 as the expanded table in DAX viz and cond is what happens when you drop the following measure

maxCondition = max('condition'[condition])

This is how you would actually solve this problem in TSQL and I wrote my dynamic DAX measure (combined) *with exactly same concept*.

For SQL and DAX enthusiasts

```
DECLARE @t1 AS TABLE ( sn int,site varchar(max),cond varchar(max),val int )
INSERT INTO @t1
SELECT * FROM
(VALUES
(1, 'site1', 'X', 100),(2, 'site1', 'Y', 200), (3, 'site1', 'Z', 300),(4, 'site1', 'X', 100 ),
(1, 'site2', 'X', 100),(2, 'site2', 'Y', 200),(3, 'site2', 'Z', 300),
(1, 'site3', 'X', 100),(2, 'site3', 'P', 200),(3, 'site3', 'Q', 300),
(1, 'site4', 'A', 100),(2, 'site4', 'Y', 200),(3, 'site4', 'Q', 300),(1, 'site5', 'E', 100),
(1, 'site5', 'E', 1000),(2, 'site5', 'F', 200),(3, 'site5', 'G', 300)
) t (a, b, c, d)
select *,
Iif(Sum(case when cond in ('X', 'Y', 'Z') then 1 end) over (partition by site) = Count(*) over (partition by site), 1, 0
) as test
from @t1
```

@jdbuchanan71 can't thank you enough for your time.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
03:01 PM

Meaning they don't have 4 months of data in the Condition table?

Is that what you are checking for? Is it always 4 months or will that change?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
03:11 PM

yes let's suppose the max month of data that can appear is 4 and condition table might or might not have 4 months of data by (site-service) based on the transaction. Howver, that would appear in the key tbl.

If it does contain 4 months of data in the condition tbl and if all those conditions are only X or Y

by site-service, I need the measure to return the max site number.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
02:27 PM

What is it about the 3 highlighted lines that means they should be excluded? That is the part I am not understanding, sorry.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
02:43 PM

@jdbuchanan71 if you drop _maxCondition measure on the matrix, you would see that it generates following values {X,Y,blank,blank} for 1000-e and {E,X,blank,blank} for 1003-e. These are not my candidates, cause the maxCondition should only be X or Y which is the case for 1000-g, 1003-g, 1005-e

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
12:56 PM

I think there is something going on with the visual in the report. Did you have it set to show items with no data at one point?

Anyway, I build a new page and modified the maxCondition measure a bit.

`maxCondition = CALCULATE(max('condition'[condition]), 'condition'[condition] IN {"X","Y"})`

And now it is behaving the way I would expect where only the rows where the measures are not blank show up.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
01:17 PM

Again thank you very much for your patience on this.

However, sadly it does not give me what I want.

Yes, I did check **Show Items with No Data** so that I don't miselad myself with a wrong measure.

*For example, the measure that you built has returned 15 instances,*

*I desired exactly 12 instances *

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
01:46 PM

And for fun, PQ solution,

```
let
Source = Table.NestedJoin(keyTbl, {"site", "month", "service"}, condition, {"site", "month", "service"}, "condition", JoinKind.LeftOuter),
#"Grouped Rows" = Table.Group(Source, {"site", "service"}, {{"ar", each _}}),
Custom = Table.AddColumn(#"Grouped Rows", "svc", each let cond = Table.Combine([ar][condition])[condition]
in List.Count(cond) = Table.RowCount([ar]) and List.Count(List.Difference({"X","Y"}, cond))=0),
#"Filtered Rows" = Table.SelectRows(Custom, each ([svc] = true)),
#"Expanded ar" = Table.ExpandTableColumn(#"Filtered Rows", "ar", {"month"}, {"month"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded ar",{"svc"})
in
#"Removed Columns"
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
01:51 PM

@CNENFRNL need a DAX measure 🙂

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
02:02 PM

Try these.

```
maxCondition =
CALCULATE (
MAX ( 'condition'[condition] ),
'condition'[condition] IN { "X", "Y" },
FILTER (
ALL ( 'condition'[site], 'condition'[service] ),
( 'condition'[site], 'condition'[service] )
IN { ( 1000, "g" ), ( 1003, "g" ), ( 1005, "e" ) }
)
)
```

```
SiteXY =
CALCULATE (
MAX ( 'condition'[site] ),
'condition'[condition] IN { "X", "Y" },
FILTER (
ALL ( 'condition'[site], 'condition'[service] ),
( 'condition'[site], 'condition'[service] )
IN { ( 1000, "g" ), ( 1003, "g" ), ( 1005, "e" ) }
)
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
02:18 PM

Sorry @jdbuchanan71 I don't want to pass on a harcoded condition in a measure to evaluate. I am working on large data set and it won't be possible for me to determine such combinations beforehand in order to hardcode them and pass on to a measure to correctly evaluate.

Which is why I built the following table to pass on to my measure to calculate the max from this which corrrectly computes in a dax query but somehow gets defetaed to a measure (I suspect due to data lineage, could be wrong though)

```
VAR _1 = ADDCOLUMNS(keyTbl,"cond",[maxCondition])
VAR _2 = FILTER(_1,[cond] in {"X","Y"})
VAR _20 = SUMMARIZE(_2,[site],[service])
VAR _3 = FILTER(_1,NOT [cond] in {"X","Y"})
VAR _30 = SUMMARIZE(_3,[site],[service])
VAR _4 = EXCEPT(_20,_30)
VAR _5 = CALCULATETABLE(keyTbl,_4)
```

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
02:00 PM

Measure is in the file. I hope you've got enough comprehension of expanded table in DAX. As you can see I intentionally abandoned "pk"; after all, data model in DAX isn't identical to database.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
04:20 PM

Sorry it is a different data model altogether. Not willling to break my existing one to smaller pieces which fantastically working otherwise only for the sake of this calculation. Want to wait to see if someone else can provide a solution.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
12:32 PM

Sorry, I am not understanding something. In the condition table under the PK 1003-3-g, the site is 1003 for condition X. What logical steps are you applying to say it should be 1000?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
12:36 PM

@jdbuchanan71 the _siteXY measure value should be 1003 and not 1000 for the example you picked. I revised the output. Sorry for the error in posting earlier.

```
| site | service | month | _maxCondition | _siteXY |
|------|---------|-------|---------------|---------|
| 1000 | g | 1 | X | 1000 |
| 1000 | g | 2 | Y | 1000 |
| 1000 | g | 3 | X | 1000 |
| 1000 | g | 4 | Y | 1000 |
| 1003 | g | 1 | X | 1003 |
| 1003 | g | 2 | Y | 1003 |
| 1003 | g | 3 | X | 1003 |
| 1003 | g | 4 | Y | 1003 |
| 1005 | e | 1 | X | 1005 |
| 1005 | e | 2 | Y | 1005 |
| 1005 | e | 3 | X | 1005 |
| 1005 | e | 4 | Y | 1005 |
```

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
12:12 PM

I think the following measure will give you what you are looking for.

`SiteXY = CALCULATE(MAX('condition'[site]),'condition'[condition] IN {"X","Y"})`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2021
12:20 PM

@jdbuchanan71 thanks for looking into it.

However, this measure returns values for 1000(site)-e(service), 1003-e and others., which is not desired. I want the measure to return values for only the following combinations

```
| site | service | month | _maxCondition | _siteXY |
|------|---------|-------|---------------|---------|
| 1000 | g | 1 | X | 1000 |
| 1000 | g | 2 | Y | 1000 |
| 1000 | g | 3 | X | 1000 |
| 1000 | g | 4 | Y | 1000 |
| 1003 | g | 1 | X | 1003 |
| 1003 | g | 2 | Y | 1003 |
| 1003 | g | 3 | X | 1003 |
| 1003 | g | 4 | Y | 1003 |
| 1005 | e | 1 | X | 1005 |
| 1005 | e | 2 | Y | 1005 |
| 1005 | e | 3 | X | 1005 |
| 1005 | e | 4 | Y | 1005 |
```

and for nothing else.

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Featured Topics

Top Solution Authors

User | Count |
---|---|

268 | |

152 | |

55 | |

51 | |

44 |

Top Kudoed Authors

User | Count |
---|---|

253 | |

168 | |

67 | |

63 | |

48 |