Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Thanks a lot,
Marcos Pérez
Solved! Go to 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
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 () )
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!
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |