cancel
Showing results for
Did you mean:
Helper III

## Compare data with conditional slicer

Hi,

I have 3 tables as belows. I wanna create slicer of customer, in which. when I choose 1 specific customer (Ex: Customer A) => It will show the the visual as "result1".

If I don't choose that slicer, it will show the visual as " result2"

So, what Dax formulas can I use ?

I

2 ACCEPTED SOLUTIONS
Solution Specialist

Hi Ngocnguyen,

This might be worth a try:

Measures:

ActualSum = SUM(CustomerActual[ActualAmount])
TargetCalculation = IF(ISFILTERED(CustomerActual),SUM(CustomerTarget[CustomerTarget]),SUM(TotalTarget[Target]))
Compare = CustomerActual[ActualSum] - TotalTarget[TargetCalculation]

The ISFILTERED funciton can be used to differentiate between the unfiltered table and when a specific customer is selected. When paired with a slicer, the TargetCalculation measure would result in the sum of the selected customer, or the sum of the TotalTarget table, based on the slicer selection.

Customer selected:

No customer selected:

Hope this helps!

Best regards,

Tim
Super User II

I prepared a solution for you.

You can create this 4 measures:

``````Selected Customer =
IF(ISFILTERED(Table1[Customer]), CONCATENATEX(DISTINCT(Table1[Customer]), Table1[Customer],","),
"ALL Customers")

Actual =
Sum(Table1[Actual Amount])

Target =
IF(ISFILTERED(Table1[Customer]),
Sum(Table2[Target]),
MAX(Table3[Target])
)

Compare = [Actual] - [Target]``````

 Proud to be a Super User.If I helped, please accept the solution and give kudos!  Linkedin

2 REPLIES 2
Super User II

I prepared a solution for you.

You can create this 4 measures:

``````Selected Customer =
IF(ISFILTERED(Table1[Customer]), CONCATENATEX(DISTINCT(Table1[Customer]), Table1[Customer],","),
"ALL Customers")

Actual =
Sum(Table1[Actual Amount])

Target =
IF(ISFILTERED(Table1[Customer]),
Sum(Table2[Target]),
MAX(Table3[Target])
)

Compare = [Actual] - [Target]``````

 Proud to be a Super User.If I helped, please accept the solution and give kudos!  Linkedin

Solution Specialist

Hi Ngocnguyen,

This might be worth a try:

Measures:

ActualSum = SUM(CustomerActual[ActualAmount])
TargetCalculation = IF(ISFILTERED(CustomerActual),SUM(CustomerTarget[CustomerTarget]),SUM(TotalTarget[Target]))
Compare = CustomerActual[ActualSum] - TotalTarget[TargetCalculation]

The ISFILTERED funciton can be used to differentiate between the unfiltered table and when a specific customer is selected. When paired with a slicer, the TargetCalculation measure would result in the sum of the selected customer, or the sum of the TotalTarget table, based on the slicer selection.

Customer selected:

No customer selected:

Hope this helps!

Best regards,

Tim

Announcements