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 di...
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.

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.

 

m3tr01d's avatar
m3tr01d
Continued Contributor
5 years ago

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