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
Anonymous
Not applicable

filtering matrix by columns

Hi, 

 

I have a matrix like this (column month row client id and value sales) and I want to filter only the rows which has non blank in each column (1000006861 and 1000011110 would be filtered)

Sin título3.png

Thanks a lot,

 

Marcos Pérez

1 ACCEPTED SOLUTION

@Anonymous 

Try placing this measure in a card visual:

 

 

BoughtInEneroAndFebrero =
VAR _BoughtInEnero =
    CALCULATETABLE ( DISTINCT ( Table1[IDCLIENTE] ), Table1[MES] = "Enero" )
VAR _BoughtInFebrero =
    CALCULATETABLE ( DISTINCT ( Table1[IDCLIENTE] ), Table1[MES] = "Febrero" )
RETURN
    COUNTROWS ( INTERSECT ( _BoughtInEnero, _BoughtInFebrero ) )

 

I'm assuming that an IDCLIENTE will only come up in the table for a specific month if there have been sales (i.e. it cannot be the case that you have a blank in IMPORTE 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @Anonymous 

This would probably be easier and more convenient if you unpivoted the month columns.

In any case, with this structure, you could create a new calculates table as follows, where Table1 is the table that you show. Add more months if necessary, following the same pattern.

 

NewTable =
FILTER (
    Table1,
    Table1[Enero] <> BLANK () && Table1[Febrero] <> BLANK ()
)
Anonymous
Not applicable

Hi @AlB, In fact I have already the data unpivotted like this. What I want is to know the number of clients who have bought in both months. Thanks! 

Sin título4.png

 

@Anonymous 

Try placing this measure in a card visual:

 

 

BoughtInEneroAndFebrero =
VAR _BoughtInEnero =
    CALCULATETABLE ( DISTINCT ( Table1[IDCLIENTE] ), Table1[MES] = "Enero" )
VAR _BoughtInFebrero =
    CALCULATETABLE ( DISTINCT ( Table1[IDCLIENTE] ), Table1[MES] = "Febrero" )
RETURN
    COUNTROWS ( INTERSECT ( _BoughtInEnero, _BoughtInFebrero ) )

 

I'm assuming that an IDCLIENTE will only come up in the table for a specific month if there have been sales (i.e. it cannot be the case that you have a blank in IMPORTE 

Anonymous
Not applicable

@AlB Thanks! It works.  Finally do you know how can I add a month slicer in the same scenario to make it dinamically? Because that measure has "Enero" and "Febrero" hardcoded. Would it be possible to do it with a slicer?

 

Thank you! You helped me a lot

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.