cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01
Super User
Super User

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

My source data is following (pbix is attached)

 

capx.PNG

 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

capy.PNG

 

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

 

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

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_0-1631317973650.png

 

View solution in original post

@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

 

capx.PNG

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.

capsql.PNG

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

19 REPLIES 19
jdbuchanan71
Super User
Super User

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_0-1631317973650.png

 

View solution in original post

@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

 

capx.PNG

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.

capsql.PNG

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

CNENFRNL
Community Champion
Community Champion

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

 

Untitled.png

jdbuchanan71
Super User
Super User

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

jdbuchanan71_0-1631311234432.png

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jdbuchanan71
Super User
Super User

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

jdbuchanan71_0-1631309215011.png

 

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jdbuchanan71
Super User
Super User

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.

jdbuchanan71_0-1631303762917.png

 

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


CNENFRNL
Community Champion
Community Champion

Screenshot 2021-09-10 223831.png

 

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"

Screenshot 2021-09-10 224523.png

@CNENFRNL  need a DAX measure 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

jdbuchanan71_0-1631307767357.png

 

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)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


CNENFRNL
Community Champion
Community Champion

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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jdbuchanan71
Super User
Super User

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?

jdbuchanan71_0-1631302254297.png

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jdbuchanan71
Super User
Super User

@smpa01 

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

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

 

jdbuchanan71_0-1631301129670.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.