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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ngocnguyen
Helper IV
Helper IV

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 ?

Thanks in advance for your support!

 

Ix.PNG

2 ACCEPTED SOLUTIONS
timg
Solution Sage
Solution Sage

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:
Capture1.PNG
 
No customer selected:
Capture.PNG
 
Hope this helps!
 
Best regards,
 
Tim




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

lkalawski
Memorable Member
Memorable Member

Hi @ngocnguyen 

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]

 

PBI_SuperUser_Rank@1x.png Proud to be a Super User.
If I helped, please accept the solution and give kudos
Linkedin

 

View solution in original post

2 REPLIES 2
lkalawski
Memorable Member
Memorable Member

Hi @ngocnguyen 

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]

 

PBI_SuperUser_Rank@1x.png Proud to be a Super User.
If I helped, please accept the solution and give kudos
Linkedin

 

timg
Solution Sage
Solution Sage

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:
Capture1.PNG
 
No customer selected:
Capture.PNG
 
Hope this helps!
 
Best regards,
 
Tim




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.