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
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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 )

Regards
Zubair

Please try my custom visuals
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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 )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thanks  

 

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.