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.
Goodday,
I have a matrix with all types of materials and not all materials are used in every project.
Basically i only want to show the colums (when i use the slicer) that have values > 0
I can't use the normal visual filters but im sure there is a way (based on my filter selection) to display all colums with values > 0
When i filter 2 projects and a specific material is only used in one of them i obviously show both projects. But when i filter a specific project and that material isn't used i do not want that column in my matrix.
Is this possible or not ?
Thanks in advance
Solved! Go to Solution.
Hi @RonaldvdH ,
You need to unpivot the columns(you could create a copy table then do this) :
= Table.UnpivotOtherColumns(#"Changed Type", {"Hoofdproject", "AP gebied naam", "2V_DAC", "12V_MDAC", "24v_LTMC", "48V_LTMC", "96V_LTMC", "192V_LTMC", "LM_EDSA-D4-48", "Woningen", "14/10 connectoren", "Markers", "14/10 eind connectoren"}, "Attribute", "Value")
Click Ctrl + the cloumn you want to use:
Then apply and create visual:
Refer:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @RonaldvdH ,
You need to unpivot the columns(you could create a copy table then do this) :
= Table.UnpivotOtherColumns(#"Changed Type", {"Hoofdproject", "AP gebied naam", "2V_DAC", "12V_MDAC", "24v_LTMC", "48V_LTMC", "96V_LTMC", "192V_LTMC", "LM_EDSA-D4-48", "Woningen", "14/10 connectoren", "Markers", "14/10 eind connectoren"}, "Attribute", "Value")
Click Ctrl + the cloumn you want to use:
Then apply and create visual:
Refer:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
HI @RonaldvdH ,
See the below if you want ,if not ,could you pls share a sample?
sample data:
use the below measure:
Measure = IF(MAX('Table'[value])=0,BLANK(),MAX('Table'[value]))
Final out put:(when choose A,B,could view a2,and when only choose A,a2 is hidden)
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
@v-luwang-msft Maybe im doing something wrong but your formula doesn't seem to work.
HI @RonaldvdH ,
Could you pls share your pbix file ?And remember to remove confidential data.
I checked my measure provided.And try to add new data as your table .
measure:
Measure 2 = IF(MAX(Table2[value])=0,BLANK(),max(Table2[value]))
And in visual ,it still work:(type3 is hidden)
Best Regards
Lucien
@v-luwang-msft your formula says that if Table2[value]=0 then return BLANK() but the value is already Blank() because in the excelfile there are no values (empty cell) so that is why I changed the formula to IF(table2[value]=BLANK();BLANK() ....
However is still doesn't work 😉
Can i E-mail you the file ?
Hi @RonaldvdH ,
You could share me by a public link. And remember to remove confidential data.
Best Regards
Lucien
Hi @v-luwang-msft ,
i think i got a similiar problem. I'd like to color the empty cells red but it just won't do it.
It seems like it skips the empty cells or doesn't even recognize that they exist?
For example:
var _value = SELECTEDVALUE(table[value])
return IF(ISBLANK(_value), "red", "white")
Help
Best Regards
Jakob
@RonaldvdH , you want filter a column or measure (example sum(Table[value]) , for the first one filter should work
example calculate(count(Table[Value]), filter(Table, not(isblank(Table[Value])) && Table[Value] >0 ))
For measure, you can use filter, visual level filter, or a measure like
sumx(filter(Table, Table[Project], Table[material], "_1", sum(Table[Value]) , [_1]>0 ) ,[_1])
@amitchandak but how does that formula or measure prevent a column from being displayed in a visual ?
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |