Leveraging Filters, Lists, Measures, etc.
I have three tables: kris_base, risks_base, and kris_risks_bridge.
From my Risks_base, in a matrix, I'm pulling in the Risk Category field that gives me a listing of Risks.
From my kris_risks_bridge, I'm pulling in a distinct count of KRI Ids.
I'm pulling in from the bridge table because this then gives me a 0 for all the Risks that don't have KRIs.
This all works.
Then this is where the break happens - I try to filter based on the active_inactive id column (where value = 1) in my kris_base table because I don't want my distinct count of KRI IDs to include KRI IDs that are inactive (value = 2).
Since there are Risks without KRIs, by applying a filter from the KRIs base table, I'm effectively dropping all Risks where the KRIs are 0.
I tried using a formula measure to apply a filter, which is what I've done in PowerBI but that doesn't seem to translate the same in Pyramid. I checked the joins with our Tech Admin and that seems to also be set up accurately. I'm looking into Lists but unsure which List would best fit this purpose.
Ultimately, I want a distinct count of active KRIs, with a value of 0 anytime there are no KRIs for all my Risks.
2 replies
-
Hi Arabi,
You can achieve this in Pyramid without a bridging table (unless you need it for other purposes):
Assuming your raw data looks like this
Kribase Table
Risk Table
Follow the following steps:
For your tables
1. Create a right outer join between risk and kribase tables
2. Create a calculated column in the model in kribase that says if active ind = 1 then 1 else 0
3. Create a discovery with risk, Active and ActiveCount. From the query ribbon click on show empties
4. 1. Then all your activecount should display as per below
Alternatively you could create a semantic calculation and aggregate all the calculated columns using the current member of the risk table - then it will force a calculation, so you wont have to use the show empties feature
1. Create a formulate – drop the aggregate function on the canvas, pick a list, select activecount, both 0 and 1
2. Pick a data point, select risk hierarchy, click on fx, and select current member, save the formula as a measure (risk count)
3. Create a discovery with risk and the risk count calculation. This will display the 0 amount without requiring show empties
Regards,
David
-
We have a bridging table because that's how our data source (saas application) works. If we stay with what we have, what would be the right joins? The right outer join would assume you want KRI base to risks. But what if we want to do it the other way around? So for every risk, tell me the KRIs. Then we'd need a right outer join from risk table to KRIs right? That's what we thought the "bidirectional" check box would do (i.e. count risks by KRIs or count KRIs by risks). Perhaps we're using that wrong? I attached a screenshot of our tables and their relationships. The blue tables are the bridge tables and the green are the dimensions.
-