Forum Discussion

svp1000's avatar
svp1000
Frequent Visitor
1 year ago
Solved

Measure issue

Hi, I have a transaction table that has Region (Ex: APAC), Country (Ex: India), and a IS_IN_SLA (Yes or No), BU, and a bunch of other columns like submission date, etc. There is a slicer on the country, date, etc

 

Now, I want to create a visual table which shows me the following - BU, In SLA - Region, In SLA - country.

 

So for example, let's say the slicer for date is filtered from 1/1/2020 to 1/12/2020. Within that filter, it should check the country selected in the country slicer, say India and calculate the In SLA country. Now from the same table it should gather that India is in APAC and calculate the In SLA - region for entire APAC. In SLA is just wherever the IS_IN_SLA is Yes

 

Dax_IN_SLA_Country =

CALCULATE(

    COUNTROWS('TRANSACTION'),

    'TRANSACTION'[IS_IN_SLA] = "Yes"

)

 

Dax_IN_SLA_Region =

VAR SelectedRegion =

    CALCULATE(

        MAX('TRANSACTION'[REGION])

    )

RETURN

CALCULATE(

    COUNTROWS('TRANSACTION'),

    'TRANSACTION'[IS_IN_SLA] = "Yes",

    'TRANSACTION'[REGION] = SelectedRegion,

    REMOVEFILTERS('TRANSACTION'[CLUSTER_NEW])

)

 

I'm facing this issue when I built it out. The country measure is simple because it respects all filters. The region measure is changing even when i select countries in the same region. FYI - There is no way to select multiple countries

  • Hi svp1000  ,

    You can try using selectedvalue in your DAX measure. This will specifically filter for the region that is selected.
    Dax_IN_SLA_Region =

     

    VAR SelectedRegion =

     

        CALCULATE(

     

            selectedvalue('TRANSACTION'[REGION])

     

        )

     

    RETURN

     

    CALCULATE(

     

        COUNTROWS('TRANSACTION'),

     

        'TRANSACTION'[IS_IN_SLA] = "Yes",

     

        'TRANSACTION'[REGION] = SelectedRegion,

     

        REMOVEFILTERS('TRANSACTION'[CLUSTER_NEW])

     

    )

     

    If you want multiple regions to be selected you can check out this solution on this post


    https://community.fabric.microsoft.com/t5/Desktop/display-multiple-elements-with-SELECTEDVALUE/m-p/650754

     

    Hope this helps. Please reach out for further assistance.

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

     

    Thank you.

5 Replies

  • could you pls provide some sample data and expected output?

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support

    Hi svp1000  ,

    You can try using selectedvalue in your DAX measure. This will specifically filter for the region that is selected.
    Dax_IN_SLA_Region =

     

    VAR SelectedRegion =

     

        CALCULATE(

     

            selectedvalue('TRANSACTION'[REGION])

     

        )

     

    RETURN

     

    CALCULATE(

     

        COUNTROWS('TRANSACTION'),

     

        'TRANSACTION'[IS_IN_SLA] = "Yes",

     

        'TRANSACTION'[REGION] = SelectedRegion,

     

        REMOVEFILTERS('TRANSACTION'[CLUSTER_NEW])

     

    )

     

    If you want multiple regions to be selected you can check out this solution on this post


    https://community.fabric.microsoft.com/t5/Desktop/display-multiple-elements-with-SELECTEDVALUE/m-p/650754

     

    Hope this helps. Please reach out for further assistance.

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

     

    Thank you.

  • v-sdhruv's avatar
    v-sdhruv
    Community Support

    Hi svp1000 ,
    Did you get a chance to try out the measure provided?
    If you are still facing any issue, consider sending a sample data with expected output/solution, so that it will be easier to approach the problem.
    Regards,
    Shruti

  • v-sdhruv's avatar
    v-sdhruv
    Community Support

    Hi svp1000 ,
    I hope your issue was addressed.
    If you still need assistance, please provide sample data so that we can address it.
    If you were able to resolve the issue, pleas Acccept it as a solution or give Kudos so that other users can benefit from it.
    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Community Support

    Hi svp1000 ,
    I hope your issue was addressed.
    If you still need assistance, please provide sample data so that we can address it.
    If you were able to resolve the issue, pleas Acccept it as a solution or give Kudos so that other users can benefit from it.
    Thank You