Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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.
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 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.
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],"")}
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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?
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.
What is it about the 3 highlighted lines that means they should be excluded? That is the part I am not understanding, sorry.
@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
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.
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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL need a DAX measure 🙂
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" ) }
)
)
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)
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
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 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 |
I think the following measure will give you what you are looking for.
SiteXY = CALCULATE(MAX('condition'[site]),'condition'[condition] IN {"X","Y"})
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |