cancel
Showing results for
Did you mean:
Highlighted
Member

## Need help to build DAX Expression

Hi all,

I have data like below table, by using this table I want to show the percentage in Card

Step 1:

any record has blank I want to skip that record (Dax expression level) and calculate the number of shipping & number of Load Type based on Concatenation Field

Step 2 :

= ((sum(shipping id) where is # distinct Shipping ID > 1 and  #distinct Load Type =1)   /  sum(overall shipment)) *100

= (7/ 17) *100

= 41% (Need to show in Card)

Step 3 :

= ((sum(shipping id) where is # distinct Shipping ID > 1 and  #distinct Load Type >1)   /  sum(overall shipment)) *100

=(5/17) *100

=29.4% (Need to show in Card)

Data:

 Shipper From To Product load type S1 LA TA P1 FL S1 LA TA P1 FL S1 LA TA P1 FL S1 LA TA P1 FL S1 LB TB P2 FL S1 LB TB P2 PL S1 LB TB P2 PL S2 LB TB P2 FL S2 LB TB P3 FL S2 Blank Blank P3 PL S2 LB Blank Blank FL S2 LB Blank P3 Blank S3 LA TA P1 FL S3 LA TA P1 FL S3 LA TA P1 FL S1 LC TC P2 PL S1 LC TC P2 FL
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Need help to build DAX Expression

Try this MEASURE for step 2 result.

It works with sample data

```Measure =
VAR ExBlanks =
FILTER (
Table1,
[Shipper] <> BLANK ()
&& [From] <> BLANK ()
&& [To] <> BLANK ()
&& [Product] <> BLANK ()
)
RETURN
SUMX (
FILTER (
SUMMARIZE (
ExBlanks,
[Shipper],
[From],
[To],
[Product],
"ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
),
[ID] > 1
),
[ID]
)
/ COUNTROWS ( Table1 )
```
Try my new Power BI game Cross the River
Super User

## Re: Need help to build DAX Expression

For step 3

```Measure 2 =
VAR ExBlanks =
FILTER (
Table1,
[Shipper] <> BLANK ()
&& [From] <> BLANK ()
&& [To] <> BLANK ()
&& [Product] <> BLANK ()
)
RETURN
SUMX (
FILTER (
SUMMARIZE (
ExBlanks,
[Shipper],
[From],
[To],
[Product],
"ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
),
[ID] > 1
),
[ID]
)
/ COUNTROWS ( Table1 )
```
Try my new Power BI game Cross the River
3 REPLIES 3
Super User

## Re: Need help to build DAX Expression

Try this MEASURE for step 2 result.

It works with sample data

```Measure =
VAR ExBlanks =
FILTER (
Table1,
[Shipper] <> BLANK ()
&& [From] <> BLANK ()
&& [To] <> BLANK ()
&& [Product] <> BLANK ()
)
RETURN
SUMX (
FILTER (
SUMMARIZE (
ExBlanks,
[Shipper],
[From],
[To],
[Product],
"ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
),
[ID] > 1
),
[ID]
)
/ COUNTROWS ( Table1 )
```
Try my new Power BI game Cross the River
Super User

## Re: Need help to build DAX Expression

For step 3

```Measure 2 =
VAR ExBlanks =
FILTER (
Table1,
[Shipper] <> BLANK ()
&& [From] <> BLANK ()
&& [To] <> BLANK ()
&& [Product] <> BLANK ()
)
RETURN
SUMX (
FILTER (
SUMMARIZE (
ExBlanks,
[Shipper],
[From],
[To],
[Product],
"ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
),
[ID] > 1
),
[ID]
)
/ COUNTROWS ( Table1 )
```
Try my new Power BI game Cross the River
Member

## Re: Need help to build DAX Expression

Thanks

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)