
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set Analysis with coalesce/if scenario
Trying to do a calculation that works on 2 pairs of columns conditionally.
I also want this to ignore all selections, but to be relevant to the current chart dimension.
Logic is thus :
Distinct Count (
If A exists
If A.state > 6 then A.number
else <null>
else
If B.state > 6 then B.number
else <null>
)
A.state | A.number | B.state | B.number | Result | Reason |
1 | apples | 1 | Mary | apples | A exists and is < 6 |
6 | bananas | 2 | John | <null> | A exists but is >= 6 |
1 | apples | 3 | Julie | apples | A exists and is < 6 |
- | - | 4 | Steve | Steve | A not exist and B <6 |
- | - | 7 | Zach | <null> | A not exist and B >=6 |
I have tried with Set Analysis but it does not like if/coalesce.
Also tried in expression, but cannot simulate the equivalent of {1}
Count(Distinct {1<coalesce([a.state],[b.state])={"<6"}>} coalesce([a.number],[b.number]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In load Script create 2 new columns using coalesce,
Load *, Coalesce(A.State,B.State) as _State_, Coalesce(A.Number,B.Number) as _Number_ resident Main_Table;
Drop Main Table;
and the use _State_ and _Number_ as Your Fields in set analysis.
or you can try this expression as measure in UI:-
=If(Coalesce(A.state,[B.state])<6,Coalesce(A.number,[B.number]))
