Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a table storing the number of clients purchases monthly, which looks like this:
Client ID | Month ID | Nb purchases |
001 | 10 | 44 |
002 | 10 | 60 |
003 | 10 | 34 |
004 | 10 | 8 |
001 | 11 | 65 |
003 | 11 | 31 |
001 | 12 | 87 |
002 | 12 | 69 |
003 | 12 | 108 |
I would like to have the possibility to filter this table on clients who appears in all months so that I could make my visuals on the same perimeter of data.
In the above table it would filter on clients 001 and 003 because they appear in all months (10, 11 and 12) of the table
I expect this result:
Client ID | Month ID | Nb purchases |
001 | 10 | 44 |
003 | 10 | 34 |
001 | 11 | 65 |
003 | 11 | 31 |
001 | 12 | 87 |
003 | 12 | 108 |
Can someone help me on this?
Thanks
Solved! Go to Solution.
You can use this measure to filter the visual or page/report.
_FilterCliente =
VAR _qtMonth = CALCULATE(DISTINCTCOUNT('Table'[Month ID]), ALL('Table'))
VAR _qtMonthClient = CALCULATE(DISTINCTCOUNT('Table'[Month ID]), ALLEXCEPT('Table', 'Table'[Client ID]))
RETURN IF(_qtMonth = _qtMonthClient, 1, 0)
You can use this measure to filter the visual or page/report.
_FilterCliente =
VAR _qtMonth = CALCULATE(DISTINCTCOUNT('Table'[Month ID]), ALL('Table'))
VAR _qtMonthClient = CALCULATE(DISTINCTCOUNT('Table'[Month ID]), ALLEXCEPT('Table', 'Table'[Client ID]))
RETURN IF(_qtMonth = _qtMonthClient, 1, 0)
Thank you! worked fine
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |