How to use slicer to filter the group data?

[MandyL]

Hi All,

I’d tried to add a Status formula to group the data and create a slicer (single select) to filter the data. However, the Type 3 group is part of Type1&2. May I know if is there any way to show the Type 3 value correctly; the type 3 value should be $4,000. Thanks!

Status = If ( ([Category] = “A” && [Design FY]= 2022), “Type 1” ,
                If ( ([Category]=”B” && [Design FY]= 2022), “Type2”,
                If ( ([Created FY]>= 2018), “Type3”, ””)))

Table

Category	Design FY	Created FY	Amount
A	2022	2017	$100
A	2022	2018	$1,000
B	2022	2018	$3,000

Result

Category	Total Amount
Type 1	$1,100
Type 2	$3,000
Type 3	$0   (The correct result should be $4,000)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[WanderingBI]

This solution might also help you

How to achieve 'or' logic but not 'and' logic with... - Microsoft Power BI Community

[WanderingBI]

Hi,

you are creating a calculated column that will show a value (the status) for each row of data.

That means that each line will get only one status assigned:

So the logic depends on your formula that consists of nested IF functions.

As an example, you could also first check if Created FY >= 2018 and then check for the status of "type 1" and "type 2" like this:

Status 2 = If ( [Created FY] >= 2018, "Type 3", IF([Category]="A" && [Design FY] = 2022, "Type 1", IF([Category]="B" && [Design FY] = 2022, "Type 2")))

So it really depends on the logic that you are following within the nested IFs.

If suspect that what you really want is to filter by two seperate logics:

1. Type 1 and 2 logic
2. Type 3 logic

You could try if two seperate calculated columns will fullfill your requirements when you add them to two slicers, otherwise i think the only other option is to adjust IF in such a way that you can have the result like "Type 2 & 3"

[MandyL]

Hi @sibanez Thanks for your feedback. I can add two separate logics. Did you know how to combine logic_1 and logic_2 into one slicer? So, I can use one slicer to filter the Type 1, Type2, and Type 3 data. Thansk!

[MandyL]

Hi @amitchandak  Did you know if there is any formula to fix this display issue? It would be great if you could help me with this request. Thanks!