Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Thiyagu
Helper III
Helper III

Conditional Formatting Slicer

Hi All,

 

It may be confusing the way i'm explaining, but the requirement is like below..

 

I have source in excel which has data in region wise Actual and Target for the whole year of 2017.. we have separate column for each month. (Below snip for reference)

Question_1.JPG

I have unpivot the whole months column and showing the values in Matrix visual.

 

Here scenario is like, If ACT is below TGT then the value should show Red and if the ACT is >= then its Green for certain KPI for ex, consider AAA and BBB. For CCC and DDD if the ACT is <= TGT then it should be Green and if ACT > TGT then it should be Red..

 

Somehow we have created the measure and put next to the value (Combined Months) column. (Below snip for reference)

Question_2.JPG

 Column green is based on KPI table, which has columns like KPI_ID, KPI_Desc, Green (Higher/Lower).. Higher means Act above the TGT, Lower means Act below the TGT (like i explained the Green and Red appearance scenario above).

 

Now where i'm struck is, we need a slicer to choose only Red KPI's i.e., wherever the Red color appear in that above image only visible rest should be blank or hide.

 

Many Thanks in Advance.. 

 

Hope you all understand my requirement.

 

 @MattAllington @Baskar @Sean @v-shex-msft

 

 

 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@Thiyagu

 

Firstly, you should pivot your months into one column:

 

1234.PNG

 

Then create a calculated column to assign value (1 or 0) when achieving target or not.

 

Tag = 
var CurrentTarget = LOOKUPVALUE('KPI'[Value],'KPI'[KPI Name],'KPI'[KPI Name],'KPI'[Year],'KPI'[Year],'KPI'[Month],'KPI'[Month],'KPI'[ACT/TARGET],"TARGET")
return
SWITCH('KPI'[Green],"Lower",IF('KPI'[Value]<CurrentTarget,1,0),"Higher",IF('KPI'[Value]>CurrentTarget,1,0))

Now you can create a matrix and apply conditional formatting on Tag column.

 

 

44.PNG

 

33.PNG

 

You can apply a slicer on Tag column to filter Red or Green.

 

Regards,

View solution in original post

12 REPLIES 12
salmankhan_hm
Frequent Visitor

Hi All,

 

I want to do something the same as the problem discussed here but my scenario is a bit different. I am having three different columns as shown in the image and the condition has been applied to all three. Now I want to have a button or slicer which I click that should only show the red color only. 

 

salmankhan_hm_0-1656941959060.png

 

Thanks in advance. 

v-sihou-msft
Employee
Employee

@Thiyagu

 

Firstly, you should pivot your months into one column:

 

1234.PNG

 

Then create a calculated column to assign value (1 or 0) when achieving target or not.

 

Tag = 
var CurrentTarget = LOOKUPVALUE('KPI'[Value],'KPI'[KPI Name],'KPI'[KPI Name],'KPI'[Year],'KPI'[Year],'KPI'[Month],'KPI'[Month],'KPI'[ACT/TARGET],"TARGET")
return
SWITCH('KPI'[Green],"Lower",IF('KPI'[Value]<CurrentTarget,1,0),"Higher",IF('KPI'[Value]>CurrentTarget,1,0))

Now you can create a matrix and apply conditional formatting on Tag column.

 

 

44.PNG

 

33.PNG

 

You can apply a slicer on Tag column to filter Red or Green.

 

Regards,

Hi @v-sihou-msft,

 

Could you also help me on the below issue i have faced, actually i have 13 columns (12months  and 1 comment).

 

Qust 1.JPG

 

So when i unpivot all columns (13) it was done because i dont have any values in comment column (Only Null). Now when i try to include some text and try the same, it pops the below errorQust 2.JPG

Final stage i'm struck. 

 

Many Thanks

 

 

 

 

I haven't gone through the entire thread, but...do you need to unpivot the Comment column? Could you just unpivot the months only? 

Hi @MalS,

 

Actually i want to show the data in matrix visual, please check the above images. I did unpivot all months with comment column as well and it works because that time comment column had null values. after i put some testing data and refresh it i faced the datatype conversion error.. So i need to show the comment part after all months like below.

 

Jan Feb Mar.... Dec.. Comments

 

Hope you understand my requirement. if you want i ll give further explanation.

 

Thanks,

THiyaga

Yes, but you could just unpivot the months (not the Comment column). 

 

Then when you create the matrix, you can add the Comment field to the rows section of the matrix. 

 

Comment Rows.png

 

 

No @MalS,

 

User requirement is to see last... Actually i did Mals but not in proper way..

 

Unpivot all months and create a calculated column to show only comments if the month is Dec else blank(). So the comment will appear only after dec. then i resize all the columns except Dec comment section and rename it.

 

Many Thanks for your efforts and work around Mals.

 

Thanks a lot. 

Alternatively, you could unpivot the Comments column with the months, then make sure the resulting Values column had a data type of Text. Then, instead of summarizing the Value field using sum, min , max (or whatever you used originally), you should be able to summarize it using 'First' (which will work on a text field).

Hi @v-sihou-msft,

 

Thank you for timely help and it works what i expected... Really Great and Many thanks

Hi @v-sihou-msft,

 

Yeah i have found why the error appears, it retrieves lot of records with same names, so i have added other two columns in lookup now it works fine.

 

But i need to show the Target row as blank, so i have tried following measure, but it gives me an error, could you sort it out.

 

Many Thanks,

Cond_Form = 
Var _Target = LOOKUPVALUE(Input[Value],Input[KPI],Input[KPI],Input[YEAR],Input[YEAR],Input[Months],Input[Months],Input[Category],Input[Category],Input[BG Name],Input[BG Name],Input[Site],Input[Site],Input[ACT/TARGET],"TGT")
Var _TGT = VALUES(Input[ACT/TARGET])
Return 
	IF(_TGT = "TGT",BLANK(),
		SWITCH(Input[Green],"Lower",IF(Input[Value] <= _Target,1,0),"Higher",IF(Input[Value]>=_Target,1,0)))

Hi @v-sihou-msft,

 

Really very happy to see the response for this query, 

I have did all the steps you have mentioned, but i get the below error while creating calc column
Question_3.JPG

 

 

 

 

"A table of multiple values was supplied where a single value was expected." 

 

Am i did anything wrong here?

 

 

Thiyagu
Helper III
Helper III

@Vvelarde @MarcelBeug@v-huizhn-msft@Greg_Deckler

 

Hope i get answer for the above query.. 😞

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.