Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrouxfrench
Frequent Visitor

Filter clients of all months

Hi
I have a table storing the number of clients purchases monthly, which looks like this:

Client IDMonth IDNb purchases
0011044
0021060
0031034
004108
0011165
0031131
0011287
0021269
00312108

 

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 IDMonth IDNb purchases
0011044
0031034
0011165
0031131
0011287
00312108

 

Can someone help me on this?

Thanks

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

@mrouxfrench ,

 

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)

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
camargos88
Community Champion
Community Champion

@mrouxfrench ,

 

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)

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Thank you! worked fine

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors