cancel
Showing results 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

Helper I

## DAX Calculation including 2 columns and slicer

Hello all, I am trying to write a DAX measure for my report where I have a table as shown below and need to show the Category and Subcategory which will be connected to my slicer. I have mentioned calculation and output required. Let me know, if more clarity is required. Thanks.

Input table

 Ticket# Category SubCategory SLA Ticket 1 A A-1 Yes Ticket 2 B B-1 Yes Ticket 3 B B-2 No Ticket 4 B B-2 No Ticket 5 A A-1 No Ticket 6 A A-2 No Ticket 7 B B-2 No Ticket 8 B B-3 Yes Ticket 9 B B-3 Yes Ticket 10 B B-1 No

calculation:

 Category SubCategory Total Tickets SLA = YES SLA% A A-1 2 1 50% A A-2 1 0 0% B B-1 2 1 50% B B-2 3 0 0% B B-3 2 2 100%

Output:

If Slicer selection is A then card visual should show

A -> A-1 50%

If Slicer selection is B then card visual should show

B -> B-3 100%

1 ACCEPTED SOLUTION
Super User

Hi @nitinbalodi
Here is a sample file with the solution https://www.dropbox.com/t/xUJUyAWUTD6YmKrI

Measures are

``Total Tickets = COUNTROWS ( Input )``
``SLA (YES) = CALCULATE ( COUNTROWS ( Input ), Input[SLA] = "Yes" )``
``SLA% = DIVIDE ( [SLA (YES)], [Total Tickets] )``
``````CARD =
VAR SummaryTable =
SUMMARIZE ( Input, Input[Category], Input[SubCategory] )
VAR MaximumSLAPercentage =
MAXX (
SummaryTable,
[SLA%]
)
VAR Result =
CONCATENATEX (
FILTER (
SummaryTable,
[SLA%] = MaximumSLAPercentage
),
Input[SubCategory] & " (" & FORMAT ( [SLA%], "#,#0.0%" ) & ")"
)
RETURN
Result``````

4 REPLIES 4
Super User

Hi @nitinbalodi
Here is a sample file with the solution https://www.dropbox.com/t/xUJUyAWUTD6YmKrI

Measures are

``Total Tickets = COUNTROWS ( Input )``
``SLA (YES) = CALCULATE ( COUNTROWS ( Input ), Input[SLA] = "Yes" )``
``SLA% = DIVIDE ( [SLA (YES)], [Total Tickets] )``
``````CARD =
VAR SummaryTable =
SUMMARIZE ( Input, Input[Category], Input[SubCategory] )
VAR MaximumSLAPercentage =
MAXX (
SummaryTable,
[SLA%]
)
VAR Result =
CONCATENATEX (
FILTER (
SummaryTable,
[SLA%] = MaximumSLAPercentage
),
Input[SubCategory] & " (" & FORMAT ( [SLA%], "#,#0.0%" ) & ")"
)
RETURN
Result``````

Helper I

Thank you @tamerj1 , this is what I was looking for...

Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Total tickets: =
COUNTROWS( Data )``````

``````SLA Yes: =
CALCULATE ( [Total tickets:], Data[SLA] = "Yes" )``````

``````SLA Yes Ratio: =
IF (
HASONEVALUE ( Data[SubCategory] ),
DIVIDE ( [SLA Yes:], [Total tickets:] ),
MAXX (
ALL ( Data[SubCategory] ),
"@SLAYes", CALCULATE ( DIVIDE ( [SLA Yes:], [Total tickets:] ) )
),
Data[SubCategory]
) & " "
& FORMAT (
MAXX (
ALL ( Data[SubCategory] ),
"@SLAYes", CALCULATE ( DIVIDE ( [SLA Yes:], [Total tickets:] ) )
),
[@SLAYes]
),
"#,#0.0%"
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

Thanks for the response, this works fine, only one issue, when I select Category "A" in slicer it is not showing "A1" as selected value instead it is showing B-3 for any selection.

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors