Need help to Create a Measure

[akshayraom]

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

[Icey · Accepted solution]

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]

@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]

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.

[m3tr01d]

You could change the order of conditions in your Swtich statement and make sure that the country conditions are in first and the GeoMarket conditions are at the end

[Fowmy]

@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
        )
    )
)

[akshayraom]

It didn’t work I’m getting a blank... I have 2 tables as below

First table is dim table and second is fact table, both are connected by Country.
I need one measure which calculates count of delivery.
For Japan and Singapore i need count of delivery which are under 3, rest i want under 6.
However when I select Greater Asia I want count of delivery under 3 and not the total count from the countries that fall under greater Asia.