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

Matrix visual filtering

Hi,

 

In below matrix visual, how do I create slicer to get the blank values?

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTI2NzNRitWB8y3MTIzB/ODEXGRpCBcu65uZnYosDeWjy5uZGJki840sTMzB/IDUktQiuIJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, SoldProductID = _t])
in
Source

 

marc_hll_0-1606276915363.png

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Please refer this measure, (Note: you need to create 0/1 table using Enter data function)

 

 

Measure 2 = 
var _select = SELECTEDVALUE('Table (2)'[Column1],1)
var _1 = 
IF(
    [Measure]=0,BLANK(),1)
var _0 = 
IF(
    [Measure]=1,BLANK(),0)
return
IF(
    _select=1,_1,_0)

 

 

ma1.jpg

 

ma2.jpg

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

View solution in original post

10 REPLIES 10
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

You can create another measure and put it in values.

 

Measure 2 = 
IF(
    [Measure]=1,BLANK(),0)

 

matrix1.jpg

 

matrix12.jpg

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Anonymous
Not applicable

Hi @v-zhenbw-msft 

Is there a way to create a slicer with 1 and 0 so that when i select 1 it shows only 1 in the matrix and when i select 0 it shows only 0 in the matrix? Instead of putting it in the values

Hi @Anonymous ,

 

Please refer this measure, (Note: you need to create 0/1 table using Enter data function)

 

 

Measure 2 = 
var _select = SELECTEDVALUE('Table (2)'[Column1],1)
var _1 = 
IF(
    [Measure]=0,BLANK(),1)
var _0 = 
IF(
    [Measure]=1,BLANK(),0)
return
IF(
    _select=1,_1,_0)

 

 

ma1.jpg

 

ma2.jpg

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

Anonymous
Not applicable

Thanks @v-zhenbw-msft 

v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Sorry for that you cannot only get the blank value in this matrix table unless you change it to a table visual.

Or you can configure some background colors in it.

Please refer the steps below.

 

1. Create a new table.

 

Table = CROSSJOIN(VALUES(Query1[Customer]),VALUES(Query1[SoldProductID]))

 

Ma1.jpg

 

2. Create a measure to get the blank value as 0.

 

Measure = 
CALCULATE(
    COUNT(Query1[Customer]),
    FILTER(
        Query1,
        Query1[Customer]=MAX('Table'[Customer])
        &&Query1[SoldProductID]=MAX('Table'[SoldProductID]))) + 0

 

Ma2.jpg

 

If it doesn’t meet your requirement, we suggest you to use the pivot function in Editor Query.

 

1. Duplicate the customer column.

 

Ma3.jpg

 

2. Then we need to pivot the productID column.

 

Ma4.jpg

 

Ma5.jpg

 

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Anonymous
Not applicable

Hi @v-zhenbw-msft 

Your first solution is close to what I want but how do I get just the 0 using a slicer?

marc_hll_0-1606886150171.png

 

amitchandak
Super User
Super User

@Anonymous , You want to get a blank value. So you have to create a measure that will convert blank to 0 and filter it

 

or try a measure  like

if(isblank(coutrows(Table)), 1, blank()) 

That will give only blank data in visual.

 

Refer :https://www.youtube.com/watch?v=roGE2qrp-eA

Anonymous
Not applicable

Hi @amitchandak, thanks for your prompt reply

But I could not get any results from your measure, is it because my original table doesn't have any blank row?

amitchandak
Super User
Super User

@Anonymous , You have create a Measure with + 0 and filter (visual level filter) for measure =0

 

Measure = count(Table[Value]) +0

 

Create a visual level filter Measure  =0

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter

Anonymous
Not applicable

sorry I didnt understand, is there any example? 

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.