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.

 

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

 

  • akshayraom's avatar
    akshayraom
    Frequent Visitor
    5 years ago

    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.