cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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  

Screenshot_1.png

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

Screenshot_2.png

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)

Screenshot_4.png

 

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)

Screenshot_5.png

 

Data:

ShipperFromToProductload type
S1LATAP1FL
S1LATAP1FL
S1LATAP1FL
S1LATAP1FL
S1LBTBP2FL
S1LBTBP2PL
S1LBTBP2PL
S2LBTBP2FL
S2LBTBP3FL
S2BlankBlankP3PL
S2LBBlankBlankFL
S2LBBlankP3Blank
S3LATAP1FL
S3LATAP1FL
S3LATAP1FL
S1LCTCP2PL
S1LCTCP2FL
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Need help to build DAX Expression

@vengadeshpalani 

 

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] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] = 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )
Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Need help to build DAX Expression

@vengadeshpalani 

 

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] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] > 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )
Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Need help to build DAX Expression

@vengadeshpalani 

 

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] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] = 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )
Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Need help to build DAX Expression

@vengadeshpalani 

 

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] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] > 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )
Try my new Power BI game Cross the River

View solution in original post

Re: Need help to build DAX Expression

Thanks  

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)