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.
Question to answer: If John in zip code 0 between Date 202001 and 202004 has ever voted = "Y"?
This is how I would right in sql but i need a measure in DAX to use the Y or N result in another measure:
MAX(CASE MEMBER='JOHN' AND VOTE='Y' THEN 'Y' ELSE 'N')
this is what I've tireid IN dax but i get error and not sure if it's correct "Cannot convert value sd014 of type text to type true/false"
VAR _MAX= IF
(
MAXX
(FILTER('TBL',
[MEMBER]= "JOHN" && [VOTE]="Y" && [DATE] >= MIN_DATE && [DATE] <= _MAX_DATE),
[ZIP]),
"YES"
, "NO")
VAR _VOTE_MBRS = CALCULATE(SUM('TBL'[MBRS]),FILTER('TBL',([DATE] >= MIN_DATE && [DATE] <= MAX_DATE) && _MAX = "YES"))
VAR _MBRS = CALCULATE(SUM('TBL'[MBRS]),FILTER('TBL',([DATE] >= MIN_DATE && [DATE] <= MAX_DATE) && _MAX = "NO"))
RETURN
SWITCH (
SELECTEDVALUE ( 'TBL'[MBRS] ),
"JOHN", _VOTE_MBRS ,
_MBRS
)
Solved! Go to Solution.
@NilR try this
Measure =
VAR _name = "John"
VAR _lower = 202001
VAR _upper = 202004
VAR _vote = "Y"
--VAR _zip = 0
VAR _count4 =
CALCULATE (
COUNT ( Votes[Vote] ),
Votes[INC_YEAR] >= _lower
&& Votes[INC_YEAR] <= _upper
&& Votes[Vote] = _vote
--&& Votes[ZIP] = _zip
)
// VAR _filt =
// IF (
// MAX ( Votes[INC_YEAR] ) >= _lower
// && MAX ( Votes[INC_YEAR] ) <= _upper
// && MAX ( Votes[MEMBER] ) = _name,
// --&& MAX ( Votes[ZIP] ) = _zip,
// "Between" & " " & _lower & " " & "and" & " " & _upper & " " & _name & " " & "with zip" & " " & MAX ( Votes[ZIP] ) & " " & "has voted" & " " & _count4 & " " & "times"
// )
VAR _test =
IF (
MAX ( Votes[INC_YEAR] ) >= _lower
&& MAX ( Votes[INC_YEAR] ) <= _upper
&& MAX ( Votes[MEMBER] ) = _name,
_count4
)
RETURN
_test
@NilR if you have a data source like following
| ZIP | INC_YEAR | MEMBER | Vote |
|-----|----------|--------|------|
| 0 | 202001 | John | N |
| 0 | 202001 | John | Y |
| 0 | 202002 | John | Y |
| 0 | 202002 | John | N |
| 0 | 202003 | John | N |
| 0 | 202004 | John | N |
| 0 | 202004 | John | N |
| 0 | 202005 | John | N |
| 1 | 202001 | John | Y |
| 0 | 202001 | Wick | Y |
You can reach following like this
_new =
VAR _name = "John"
VAR _lower = 202001
VAR _upper = 202004
VAR _vote = "Y"
VAR _zip = 0
VAR _count4 =
CALCULATE (
COUNT ( Votes[Vote] ),
Votes[INC_YEAR] >= _lower
&& Votes[INC_YEAR] <= _upper
&& Votes[Vote] = _vote
&& Votes[ZIP] = _zip
)
VAR _filt =
IF (
MAX ( Votes[INC_YEAR] ) >= _lower
&& MAX ( Votes[INC_YEAR] ) <= _upper
&& MAX ( Votes[MEMBER] ) = _name
&& MAX ( Votes[ZIP] ) = _zip,
"Between" & " " & _lower & " " & "and" & " " & _upper & " " & _name & " " & "with zip" & " " & _zip & " " & "has voted" & " " & _count4 & " " & "times"
)
RETURN
_filt
This is exactly what I needed, Thank you!
One quetion. I do not have the zip code filter but want to group by zipcode. how can I apply into your formula?
@NilR try this
Measure =
VAR _name = "John"
VAR _lower = 202001
VAR _upper = 202004
VAR _vote = "Y"
--VAR _zip = 0
VAR _count4 =
CALCULATE (
COUNT ( Votes[Vote] ),
Votes[INC_YEAR] >= _lower
&& Votes[INC_YEAR] <= _upper
&& Votes[Vote] = _vote
--&& Votes[ZIP] = _zip
)
// VAR _filt =
// IF (
// MAX ( Votes[INC_YEAR] ) >= _lower
// && MAX ( Votes[INC_YEAR] ) <= _upper
// && MAX ( Votes[MEMBER] ) = _name,
// --&& MAX ( Votes[ZIP] ) = _zip,
// "Between" & " " & _lower & " " & "and" & " " & _upper & " " & _name & " " & "with zip" & " " & MAX ( Votes[ZIP] ) & " " & "has voted" & " " & _count4 & " " & "times"
// )
VAR _test =
IF (
MAX ( Votes[INC_YEAR] ) >= _lower
&& MAX ( Votes[INC_YEAR] ) <= _upper
&& MAX ( Votes[MEMBER] ) = _name,
_count4
)
RETURN
_test
Try this
TestExists =
VAR tblYesVotes =
CALCULATETABLE(
SUMMARIZE(
Votes,
Votes[ZIP CODE],
Votes[MEMBER]
),
Votes[INC_YEAR] >= 202001 && Votes[INC_YEAR] <= 202004,
Votes[VOTE] = "Y"
)
RETURN IF( ISEMPTY( tblYesVotes ), "N", "Y")
Thank you! I get an erro that 'tblYesVotes' is a table name and cannot be used to define a variable. 😞
That's odd. You don't already have a table named that in your model? Try given the variable a different name?
just to check, you are creating a measure?
Thank you! changing the name and see if it works.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |