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

[smpa01]

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 site-service-month

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

[jdbuchanan71 · Accepted solution]

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 )

[jdbuchanan71 · Accepted solution]

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 )

[smpa01 · Accepted solution]

@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.

[CNENFRNL]

Hmm... why not leave this simple question to Excel formula, our oldie but goodie

{=IF(COUNT(0/((cond[site]=[@site])*(cond[service]=[@service])*(cond[condition]={"x","y"})))=COUNTIFS([site],[@site],[service],[@service]),[@site],"")}

[jdbuchanan71]

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?

[smpa01]

@jdbuchanan71 

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.

[jdbuchanan71]

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

[smpa01]

@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

[jdbuchanan71]

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.

[smpa01]

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

[CNENFRNL]

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"

[smpa01]

@CNENFRNL  need a DAX measure 🙂

[jdbuchanan71]

@smpa01 

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" ) }
    )
)

[smpa01]

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)

[CNENFRNL]

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.

[smpa01]

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.

[jdbuchanan71]

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?

[smpa01]

@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    |

[jdbuchanan71]

@smpa01 

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

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

[smpa01]

@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.