Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to summarise the performance of kiosks within shopping malls using a measure. If a Kiosk Type is existing plus clean then this is considered a compliant Kiosk Type within a mall.
For example Kiosk Type A in Sunnyside Shopping Mall is compliant as it exists and clean.
However Kiosk Type B within the same Sunnyside Shopping mall is not compliant as the second Kiosk Type B (Kiosk TypeB2) is not clean.
My data looks like the following.
Mall Unique Code | Shop Name | Kiosk Type | Kiosk Type Unique Code | Question | Answer |
1 | Bayswater Shopping Mall | KIOSK Type A | Kiosk Type A | Does this exist? | No |
1 | Bayswater Shopping Mall | KIOSK Type A | Kiosk Type A | Is this clean? | No |
1 | Bayswater Shopping Mall | KIOSK Type B | KIOSK Type B | Does this exist? | Yes |
1 | Bayswater Shopping Mall | KIOSK Type B | KIOSK Type B | Is this clean? | Yes |
1 | Bayswater Shopping Mall | KIOSK Type B | KIOSK Type B2 | Does this exist? | Yes |
1 | Bayswater Shopping Mall | KIOSK Type B | KIOSK Type B2 | Is this clean? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type A | Kiosk Type A | Does this exist? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type A | Kiosk Type A | Is this clean? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type B | KIOSK Type B | Does this exist? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type B | KIOSK Type B | Is this clean? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type B | KIOSK Type B2 | Does this exist? | Yes |
2 | Sunnyside Shopping Mall | KIOSK Type B | KIOSK Type B2 | Is this clean? | No |
The output I desire is below in this summary table.
Kiosk Type | Number of Compliant Malls |
KIOSK Type A | 1 |
KIOSK Type B | 1 |
Kiosk Type A is compliant in Sunnyside Shopping Mall. (clean and existing)
Kiosk Type B is compliant in Bayswater Shopping Mall as both Kiosk Type Bs are compliant. (clean and existing)
My attempt
Measure 1
Kiosk Count
=
CALCULATE (
COUNTROWS ( Table1 ),
Table1[Question] = "Does this exist?",
Table1[Answer ] = "Yes",
ALLEXCEPT ( Table1, Table1[Mall Unique Code], Table1[Kiosk Type] )
)
Measure 2
Clean Count=
CALCULATE (
COUNTROWS ( Table1 ),
Table1[Question] = "Is this clean?",
Table1[Answer ] = "Yes",
ALLEXCEPT ( Table1, Table1[Mall Unique Code], Table1[Kiosk Type] )
)
Measure 3
If I pivot the Kiosk type and the second heirachy is by mall and I count the 1s (which represent 100%). I get the result I desire (just not in the above summary table)
I am trying to avoid using calculated columns and was wondering if it is possible to roll up the compliance using measures only in the summary table?
Any help would be appreciated.
Thank you all!
Solved! Go to Solution.
I think I figured this one out.
The following measure provides the result I am after
Number of Compliant Malls:=CALCULATE(DISTINCTCOUNT('Table1'[Mall Unique Code]),FILTER('Table1',[Measure 3]>=1))
If anyone has a different way of calculating this, I would be interested in hearing this.
Thank you all.
I think I figured this one out.
The following measure provides the result I am after
Number of Compliant Malls:=CALCULATE(DISTINCTCOUNT('Table1'[Mall Unique Code]),FILTER('Table1',[Measure 3]>=1))
If anyone has a different way of calculating this, I would be interested in hearing this.
Thank you all.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |