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.
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 ) )
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |