cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markoss92 Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: filtering matrix by columns

@markoss92 

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
Super User
Super User

Re: filtering matrix by columns

Hi @markoss92 

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 ()
)
markoss92 Regular Visitor
Regular Visitor

Re: filtering matrix by columns

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

 

Super User
Super User

Re: filtering matrix by columns

@markoss92 

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

markoss92 Regular Visitor
Regular Visitor

Re: filtering matrix by columns

@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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,138 guests
Please welcome our newest community members: