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.
Hey guys,
I need some help trying to count how many times that multiple condition occur based upon a distinct count. e.g.
Invoice | Charge Type
123 | Basic
123 | Intermediate
124 | Basic
124 | intermediate
124 | Complex
I would like the count of this to be 1 because there is 1 invoice with all 3 charge types. Im not sure if this makes sense sorry I am only new to this whole thing. Any help is appreciated. Thank you
Solved! Go to Solution.
I am assuming when you say "blanks" you mean the Charge type being blank? Try this measure
Invoices with 3 Charges = COUNTROWS ( FILTER ( VALUES ( Table1[Invoice ] ), CALCULATE ( COUNTROWS ( CALCULATETABLE ( VALUES ( Table1[Charge Type] ), Table1[Charge Type] <> BLANK () ) ) ) = 3 ) )
Hi,
This measure will count the number of invoices where the the Charge Type is =3. The answer is 1.
Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Invoice]),[Invoice],"ABCD",DISTINCTCOUNT(Data[Charge Type])),[ABCD]=3))
Hope this helps.
@Anonymous
It would nice if I can see your expected result.
For now you can try this measure. It counts only those invoices which have 3 unique charge types
Invoices with 3 Charges = COUNTROWS( FILTER( VALUES(Table1[Invoice ]), CALCULATE(COUNTROWS(VALUES(Table1[Charge Type])))=3 ) )
Hope it helps
That definetly helps thank you.
The only thing that that does is also count blanks. What is the easiest way for it not to count the blank ones
I am assuming when you say "blanks" you mean the Charge type being blank? Try this measure
Invoices with 3 Charges = COUNTROWS ( FILTER ( VALUES ( Table1[Invoice ] ), CALCULATE ( COUNTROWS ( CALCULATETABLE ( VALUES ( Table1[Charge Type] ), Table1[Charge Type] <> BLANK () ) ) ) = 3 ) )
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
101 | |
92 | |
74 | |
60 | |
59 |