Forum Discussion

govind_021's avatar
govind_021
Super User
1 year ago
Solved

Dax

Hi Dax experts ,
I have one scenerio pleae help me ,
if you see i have parameter and has three categories year , quarter and category 
it is not always the case that thse selection will be in the particular order it can be any order so heirachy is not fixed
what i want is the total of lower heirarchy currently the last one is quarter so , 20,2035 should appear for each row of 2017
and it no fixed category can also be last hierarchy depending upon user selection.
so please help me out with this

please acces demo file here : https://drive.google.com/file/d/1WcxgDWHaG_DcwacmgdSS0z0rd01OKZY3/view?usp=sharing

Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi govind_021, 
    Thank you for reaching out to the Microsoft fabric community forum. Thank you @ for your input on this issue. 
    After thoroughly reviewing the details you provided

    Dax Measure: 

    Check = 
SWITCH(
    TRUE(),
    ISINSCOPE('Date'[QuarterInCalendar]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[QuarterInCalendar])),
    ISINSCOPE('Date'[Year]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[Year])),
    ISINSCOPE('Product'[Category]),
        CALCULATE([Total Sales], REMOVEFILTERS('Product'[Category])),
    [Total Sales]
)

    Output: 

     


    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
     
    Thank you.

6 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello govind_021 

     

    Try this measure

    Dynamic Lowest Level Total =

    VAR VisibleColumns = SELECTCOLUMNS(VALUES('YourTable'), "Column", "YourColumnName")

    VAR IsQuarterVisible = ISINSCOPE('YourTable'[Quarter])

    VAR IsYearVisible = ISINSCOPE('YourTable'[Year])

    VAR IsCategoryVisible = ISINSCOPE('YourTable'[Category])

     

    RETURN

    SWITCH(

        TRUE(),

        NOT IsQuarterVisible && NOT IsCategoryVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Year])),

       

        NOT IsQuarterVisible && NOT IsYearVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Category])),

       

        NOT IsYearVisible && NOT IsCategoryVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Quarter])),

     

        NOT IsQuarterVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Quarter])),

     

        NOT IsYearVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Year])),

     

        NOT IsCategoryVisible,

            CALCULATE([Total Sales], REMOVEFILTERS('YourTable'[Category])),

     

        -- If all are visible, assume the last is the most granular

        CALCULATE([Total Sales])

    )

     

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

  • govind_021's avatar
    govind_021
    Super User
    1 year ago

    Hi pankajnamekar25 
    your solution is  not working at all.
    i have given the link for file , can you please try from your end and see if it works.

    • v-saisrao-msft's avatar
      v-saisrao-msft
      Community Support
      1 year ago

      Hi govind_021, 
      Thank you for reaching out to the Microsoft fabric community forum. Thank you @ for your input on this issue. 
      After thoroughly reviewing the details you provided

      Dax Measure: 

      Check = 
SWITCH(
    TRUE(),
    ISINSCOPE('Date'[QuarterInCalendar]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[QuarterInCalendar])),
    ISINSCOPE('Date'[Year]),
        CALCULATE([Total Sales], REMOVEFILTERS('Date'[Year])),
    ISINSCOPE('Product'[Category]),
        CALCULATE([Total Sales], REMOVEFILTERS('Product'[Category])),
    [Total Sales]
)

      Output: 

       


      If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
       
      Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi govind_021,
    I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi govind_021,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi govind_021,

     

    We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

     

    Thank you.