Forum Discussion
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
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)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.
10 Replies
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 , 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.
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?
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]))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]))) + 0If it doesn’t meet your requirement, we suggest you to use the pivot function in Editor Query.
1. Duplicate the customer column.
2. Then we need to pivot the productID column.
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.
Your first solution is close to what I want but how do I get just the 0 using a slicer?
Hi Anonymous ,
You can create another measure and put it in values.
Measure 2 = IF( [Measure]=1,BLANK(),0)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.
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)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.
