Forum Discussion

akshayraom's avatar
akshayraom
Frequent Visitor
5 years ago
Solved

Need help to Create a Measure

Hi All Im new to dax , i need to create a measure which will calculate count of delivery days based on slicer selection. I have Market and country in slicer. But i need to count different days for different country and geo market, So each country has its own calculation for example , when japan is selected I need count of delivery days under 3, Similarly when Singapore is selected I need count of delivery days under 4, however when Greater Asia is selcted I need count of delivery days under 2.   PLease Help.. Thank You

 

 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi akshayraom ,

     

    I create a simple sample based on your description. Please check if the attched file is what you want.

    Measure =
IF (
    ISFILTERED ( 'dim table'[Country] ),
    SWITCH (
        SELECTEDVALUE ( 'dim table'[Country] ),
        "Japan", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 3 ) ),
        "Singapore", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 4 ) ),
        COUNTROWS ( FILTER ( 'fact table', [Delivery] < 6 ) )
    ),
    IF (
        ISFILTERED ( 'dim table'[Geo Market] ),
        SWITCH (
            SELECTEDVALUE ( 'dim table'[Geo Market] ),
            "Greater Asia", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 2 ) )
        )
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi akshayraom ,

     

    I create a simple sample based on your description. Please check if the attched file is what you want.

    Measure =
IF (
    ISFILTERED ( 'dim table'[Country] ),
    SWITCH (
        SELECTEDVALUE ( 'dim table'[Country] ),
        "Japan", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 3 ) ),
        "Singapore", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 4 ) ),
        COUNTROWS ( FILTER ( 'fact table', [Delivery] < 6 ) )
    ),
    IF (
        ISFILTERED ( 'dim table'[Geo Market] ),
        SWITCH (
            SELECTEDVALUE ( 'dim table'[Geo Market] ),
            "Greater Asia", COUNTROWS ( FILTER ( 'fact table', [Delivery] < 2 ) )
        )
    )
)

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    akshayraom 

    You can add a Delivery Days column in the Country Table and refer to that number and use it in your calculation. 
    Set the Selection to Single in the above Slicer and use as follows in your calculation.

    SELECTEDVALUE ( COUNTRY[DELIVERY DAYS] )




    • akshayraom's avatar
      akshayraom
      Frequent Visitor
      5 years ago

      Hi Fowmy
      thank you for the  reply .. Im using mapping table for countries from a sql server, so i wont be able to modify that.  Here is the code im using.

       

      Delivery #.1 = SWITCH(
      TRUE(),
      SELECTEDVALUE('BMT Geo_Hierarchy'[Geo Market]) = "Greater Asia",CALCULATE(COUNT(Supply_Orders_APJ[Delivery]),Supply_Orders_APJ[Delivery]<=3),
      SELECTEDVALUE('BMT Geo_Hierarchy'[Country]) = "Singapore",CALCULATE(COUNT(Supply_Orders_APJ[Delivery]),Supply_Orders_APJ[Delivery]<=3),
      SELECTEDVALUE('BMT Geo_Hierarchy'[Country]) = "Japan",CALCULATE(COUNT(Supply_Orders_APJ[Delivery]),Supply_Orders_APJ[Delivery]<=3),
      CALCULATE(COUNT(Supply_Orders_APJ[Delivery]),Supply_Orders_APJ[Delivery]<=6))

      And if you look at the below table Greater Asia's condition is getting applied to all the countries which come under Greater Asia, as u see final total numbers of all countries  is same as Greater Asia number so i need something to apply only to Greater Asia and not the countries which come under it.

       

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        akshayraom 

        Having difficulty understanding the question, can you try this

        Delivery #.1 =
IF (
    NOT (ISFILTERED ( 'BMT Geo_Hierarchy'[Country] ) ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'BMT Geo_Hierarchy'[Geo Market] )
            IN { "Greater Asia", "Singapore", "Japan" },
            CALCULATE (
                COUNT ( Supply_Orders_APJ[Delivery] ),
                Supply_Orders_APJ[Delivery] <= 3
            ),
        CALCULATE (
            COUNT ( Supply_Orders_APJ[Delivery] ),
            Supply_Orders_APJ[Delivery] <= 6
        )
    )
)