Forum Discussion
Slicer conditional formatting value border color
Hi everyone!
Background:
I have a report that gives me an overview about a file migration, I use a slicer to filter for the extension of a file:
It should be easy for users (and me) to see which file types are not supported. I have a second table where I can fill in all supported extensions:
I created a column using the following formular:
Extension Check =
VAR _SEL =
SELECTCOLUMNS('Valid Extension',"Title",[Titel])
RETURN
IF(Scan[SourceExtension] IN _SEL, 0, 1)This gives me a column with 0 if the extension is in the list and an 1 if it's not.
To make it easier to immediatly see the "not supported" extensions, I want to have something visual within the slicer. The only option I found was the "Border color" within "Values":
The problem:
I want to use the "conditional formatting" option to change the color depending on the supported / not supported extensions from the other list. e.g.:
But within the conditional formatting window, I need to select a summarization, so my values within the column are not working:
My calculated column is formatted as:
Does someone has an idea how to accomplish this?
Thanks in advance!
Marvin
I didn't find any solution how to do the formatting, if someone has an idea, feel free to answer.
I came around with this solution, that works for me:
I created another table using
Extension Check = DISTINCT(SELECTCOLUMNS(Scan,"Extension",[SourceExtension]))So I just have a distinct list of all my extensions. Then I use the same formular as above to create a new column:
Extension Check = VAR _SEL = SELECTCOLUMNS('Valid Extension',"Title",[Title]) RETURN IF('Extension Check'[Extension] IN _SEL, "Supported", "Unsupported")But this time I put in "Supported" and "Unsupported". This gives me a list with all distinct values and a column if it's supported or not. Using the relationship between the "Scan" and "Extension Check" table using 1-* on the column extension, I can get use the slicer with a second level:
This also helps me to easily select all unsupported or supported extension and solves my problem!
Best regard
Marvin
1 Reply
I didn't find any solution how to do the formatting, if someone has an idea, feel free to answer.
I came around with this solution, that works for me:
I created another table using
Extension Check = DISTINCT(SELECTCOLUMNS(Scan,"Extension",[SourceExtension]))So I just have a distinct list of all my extensions. Then I use the same formular as above to create a new column:
Extension Check = VAR _SEL = SELECTCOLUMNS('Valid Extension',"Title",[Title]) RETURN IF('Extension Check'[Extension] IN _SEL, "Supported", "Unsupported")But this time I put in "Supported" and "Unsupported". This gives me a list with all distinct values and a column if it's supported or not. Using the relationship between the "Scan" and "Extension Check" table using 1-* on the column extension, I can get use the slicer with a second level:
This also helps me to easily select all unsupported or supported extension and solves my problem!
Best regard
Marvin