Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody! I can't GROUPBY my count of my purchases by count of my clients.
That's user table:
That's purchase's table:
And that's what I want:
All this data must be filtered by date slice!
Help me please)
Solved! Go to Solution.
Hi @EgorS
try this approach.
See the attached file as well
First create a calculated table
NewTable = SELECTCOLUMNS ( GENERATESERIES ( 1, MAX ( Purchases[Count of Purchases] ) ), "Count of Purchases", [Value] )
Then a Measure
No. of users = COUNTROWS ( FILTER ( VALUES ( Purchases[UserId] ), CALCULATE ( COUNT ( Purchases[UserId] ) ) = SELECTEDVALUE ( NewTable[Count of Purchases] ) ) )
Hi @EgorS
In your Purchases Table, you can add this calculated column
Please see attached file with your sample data
Count of Purchases = CALCULATE ( COUNT ( Purchases[UserId] ), ALLEXCEPT ( Purchases, Purchases[UserId] ) )
Then in Table Visual, you can drag the above calculated column and DistinctCount of UserId to get the desired result
@Zubair_Muhammad Thank you for answer) But it work wrong when I filtring data with date slicer! Please check my example: https://www.dropbox.com/s/apwwg61aq7ty9or/ExampleWithDate.pbix?dl=0
Hi @EgorS
try this approach.
See the attached file as well
First create a calculated table
NewTable = SELECTCOLUMNS ( GENERATESERIES ( 1, MAX ( Purchases[Count of Purchases] ) ), "Count of Purchases", [Value] )
Then a Measure
No. of users = COUNTROWS ( FILTER ( VALUES ( Purchases[UserId] ), CALCULATE ( COUNT ( Purchases[UserId] ) ) = SELECTEDVALUE ( NewTable[Count of Purchases] ) ) )
@Zubair_Muhammad , this is so great! But there is one thing: is there any way to filter list of users, when I click on the cell of "Count of Purchases"?
I think you can establish a relationship between the columns "Count of Purchases" in Purchases and New Table
Then it would work
@Zubair_Muhammad Thank you! It works! I will try this on a large amount of data 😃
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |