Forum Discussion
Multiple filters, if and count
Hi I`m pretty new to Power I and i have a prolem that i can`t solve. I have different customer names and when they got a discount, i want to show that John is repeated 5, times for example, and 3 times he got a discount, Mary is repeated 3 times and she got a discount only 1 times. At the end i want to show only the once that got a discount more than 2 times in a tale in the report view. I tried with if and count in my exxcel base and then just to show it in the report view of power bi, but it showed that John is repeated a total of 5 times and i want to show only the times with the discount. The table must show John 3 times and everybody else with more than 2 discounts. And i want to show other column, in the report view, that shows the total numer of purchases nomatter if they had a discount or not.
I hope that i eplained it the right way.
Thanks in advance.
This measure should work:
Customers with ore than 2 discounts = VAR _DC = CALCULATE ( COUNTROWS ( fTable ), FILTER ( ALLEXCEPT ( fTable, fTable[Customer] ), fTable[Discount] = "Yes" ) ) RETURN IF ( _DC >= 2, _DC )
5 Replies
PaulDBrownCommunity Champion
Can you please post some sample non-confidential data?
Lets say my table is something like this, but bigger
Customer Purchase Discount John Olivia Marry yes yes Tomas yes John yes yes Olivia yes yes John yes John yes yes John yes yes John yes yes John John yes Tomas Marry yes yes Marry yes yes Tomas yes Tomas yes Tomas Marry yes Olivia yes At the end i want to show only the once with purchases and more than 2 discount and in this case the new tale would be
Marrry 3
John 4
Something like that. It`s a bit difficult to explain without the real table, but i hope this will help.
How can i count a measure? Maybe i have found a way to do what i want with first making a measure for the count of repetition of the names and now i need to count this measure only the times it`s repeated more than 5 times.
- PaulDBrown
This measure should work:
Customers with ore than 2 discounts = VAR _DC = CALCULATE ( COUNTROWS ( fTable ), FILTER ( ALLEXCEPT ( fTable, fTable[Customer] ), fTable[Discount] = "Yes" ) ) RETURN IF ( _DC >= 2, _DC )
v-henryk-mstfCommunity Support
Hi dchakarova ,
Whether the advice given by PaulDBrown has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
